test as (

select

date,

chain,

tvl_usd as "Last TVL",

LEAD(tvl_usd, 1) OVER (ORDER BY chain, date DESC) as "TVL 1D",

LEAD(tvl_usd, 3) OVER (ORDER BY chain, date DESC) as "TVL 3D",

LEAD(tvl_usd, 7) OVER (ORDER BY chain, date DESC) as "TVL 7D",

LEAD(tvl_usd, 14) OVER (ORDER BY chain, date DESC) as "TVL 14D",

LEAD(tvl_usd, 21) OVER (ORDER BY chain, date DESC) as "TVL 21D",

LEAD(tvl_usd, 30) OVER (ORDER BY chain, date DESC) as "TVL 30D"

-- (("Last TVL"-"TVL 1D")/("TVL 1D")) as "% 1D"

-- (("Last TVL"-"TVL 3D")/("TVL 3D")) as "% 3D",

-- (("Last TVL"-"TVL 7D")/("TVL 7D")) as "% 7D"

-- LEAD(tvl_usd, 14) OVER (ORDER BY chain, date DESC) as "TVL 14D"

-- LEAD(tvl_usd, 21) OVER (ORDER BY chain, date DESC) as "TVL 21D",

-- LEAD(tvl_usd, 30) OVER (ORDER BY chain, date DESC) as "TVL 30D"

from

external.defillama.fact_chain_tvl

where

date >= dateadd(day, -31, current_date())

),

--,

-- where date > '2023-02-01'),

ranked_messages AS (

SELECT

m.*,

ROW_NUMBER() OVER (PARTITION BY chain ORDER BY date DESC) AS rn

FROM

test AS m

)

SELECT

date,