SELECT

chain,

LAG(tvl_usd, 1) OVER (PARTITION BY chain ORDER BY date) AS previous_tvl_usd,

LAG(tvl_usd, 7) OVER (PARTITION BY chain ORDER BY date) AS weekly_tvl_usd,

LAG(tvl_usd, 30) OVER (PARTITION BY chain ORDER BY date) AS monthly_tvl_usd,

MAX(date) OVER (PARTITION BY chain) AS latest_date

FROM external.defillama.fact_chain_tvl

WHERE date >= DATEADD('day', -30, CURRENT_DATE()) AND chain != 'Wax'

),

latest_tvl AS (

SELECT

chain,

MAX(date) AS latest_date,

MAX(tvl_usd) AS current_tvl,

LAG(MAX(tvl_usd), 1) OVER (PARTITION BY chain ORDER BY MAX(date)) AS previous_tvl_usd

FROM external.defillama.fact_chain_tvl

WHERE date >= DATEADD('day', -1, CURRENT_DATE()) AND chain != 'Wax'

GROUP BY chain

SELECT

a.date,

a.chain,

MAX(a.tvl_usd) AS current_tvl,

-- MAX(a.tvl_usd) - MAX(b.previous_tvl_usd) AS daily_change,

MAX(a.tvl_usd) - MAX(b.weekly_tvl_usd) AS weekly_change,

MAX(a.tvl_usd) -MAX(b.monthly_tvl_usd) as monthly_change,

((MAX(a.tvl_usd)+MAX(b.monthly_tvl_usd)-MAX(a.tvl_usd))/MAX(a.tvl_usd)) as monthly_pct_change

FROM external.defillama.fact_chain_tvl a

JOIN daily b ON a.chain = b.chain AND a.date = b.latest_date

JOIN latest_tvl c ON a.chain = c.chain AND a.date = c.latest_date

WHERE a.chain != 'Wax'

GROUP BY a.chain, a.date

having MAX(a.tvl_usd) > 0

ORDER BY MAX(a.tvl_usd) DESC