SELECT

date,

protocol_id,

protocol,

SPLIT_PART(chain, '-', 1) as clean_chain, -- This will take first part before any '-'

category,

SUM(chain_tvl) as total_tvl

FROM external.defillama.fact_protocol_tvl

WHERE date BETWEEN CURRENT_DATE() - 90 AND CURRENT_DATE()

GROUP BY date, protocol_id, protocol, SPLIT_PART(chain, '-', 1), category

),

tvl_metrics AS (

SELECT

protocol,

category,

clean_chain as chain,

-- Current TVL (most recent date)

FIRST_VALUE(total_tvl) OVER (PARTITION BY protocol, clean_chain ORDER BY date DESC) as current_tvl,

-- TVL changes over different periods

FIRST_VALUE(total_tvl) OVER (PARTITION BY protocol, clean_chain ORDER BY date DESC) -

NTH_VALUE(total_tvl, 2) OVER (PARTITION BY protocol, clean_chain ORDER BY date DESC) as day_1_change,

FIRST_VALUE(total_tvl) OVER (PARTITION BY protocol, clean_chain ORDER BY date DESC) -

NTH_VALUE(total_tvl, 8) OVER (PARTITION BY protocol, clean_chain ORDER BY date DESC) as day_7_change,

FIRST_VALUE(total_tvl) OVER (PARTITION BY protocol, clean_chain ORDER BY date DESC) -

NTH_VALUE(total_tvl, 31) OVER (PARTITION BY protocol, clean_chain ORDER BY date DESC) as day_30_change,

FIRST_VALUE(total_tvl) OVER (PARTITION BY protocol, clean_chain ORDER BY date DESC) -

NTH_VALUE(total_tvl, 91) OVER (PARTITION BY protocol, clean_chain ORDER BY date DESC) as day_90_change,

-- Volatility (Standard Deviation of daily changes over the period)

STDDEV(total_tvl) OVER (PARTITION BY protocol, clean_chain) as tvl_volatility,