SELECT DISTINCT DAY, ps.POOL_NAME FROM thorchain.defi.fact_daily_pool_stats

CROSS JOIN (

SELECT DISTINCT POOL_NAME FROM thorchain.defi.fact_daily_pool_stats

WHERE POOL_NAME LIKE '%USDT%'

) AS ps

, pools AS (

SELECT *

FROM thorchain.defi.fact_daily_pool_stats

WHERE POOL_NAME LIKE '%.USDT%'

, savers AS (

SELECT *

FROM thorchain.defi.fact_daily_pool_stats

WHERE POOL_NAME LIKE '%/USDT%'

d.DAY,

d.POOL_NAME,

COALESCE(p.ASSET_LIQUIDITY, 0) AS POOL_DEPTH,

SUM(COALESCE(p.ASSET_LIQUIDITY, 0))

OVER (PARTITION BY d.DAY, d.POOL_NAME ORDER BY d.POOL_NAME RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

AS TOTAL_POOLS_DEPTH,

COALESCE(s.ASSET_LIQUIDITY, 0) AS SAVERS_DEPTH,

SUM(COALESCE(s.ASSET_LIQUIDITY, 0))

OVER (PARTITION BY d.DAY, d.POOL_NAME ORDER BY d.POOL_NAME RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

AS TOTAL_SAVERS_DEPTH,

COALESCE(p.SYSTEM_REWARDS_USD, 0) AS POOL_BLOCK_REWARDS,

COALESCE(p.TOTAL_SWAP_FEES_USD, 0) AS POOL_LIQUIDITY_FEES,

SUM(COALESCE(p.SYSTEM_REWARDS_USD, 0) + COALESCE(p.TOTAL_SWAP_FEES_USD, 0))

OVER (PARTITION BY d.DAY, d.POOL_NAME ORDER BY d.POOL_NAME RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

AS TOTAL_POOL_EARNINGS,

COALESCE(s.SYSTEM_REWARDS_USD, 0) AS SAVERS_BLOCK_REWARDS,

COALESCE(s.TOTAL_SWAP_FEES_USD, 0) AS SAVERS_LIQUIDITY_FEES,

SUM(COALESCE(s.SYSTEM_REWARDS_USD, 0) + COALESCE(s.TOTAL_SWAP_FEES_USD, 0))