SELECT

hour::date AS date,

'WKLAY' AS symbol,

'0x57d1a61e4fd09fbf16e35b04959e94dcf2032974' AS token_address,

AVG(price) AS token_price

FROM

kaia.price.ez_prices_hourly

WHERE

symbol = 'KLAY'

AND hour::date >= current_date - interval '1 year'

GROUP BY

1, 2, 3

),

tvl_data AS (

SELECT

a.DATE AS date,

a.TVL_USD AS current_tvl,

a.TVL_USD / b.token_price AS current_tvl_kaia,

LAG(a.TVL_USD, 1) OVER (ORDER BY a.DATE) AS tvl_1_Day_ago,

LAG(a.TVL_USD, 7) OVER (ORDER BY a.DATE) AS tvl_1_week_ago,

LAG(a.TVL_USD, 30) OVER (ORDER BY a.DATE) AS tvl_1_month_ago,

LAG(a.TVL_USD, 90) OVER (ORDER BY a.DATE) AS tvl_3_months_ago

FROM

external.defillama.fact_chain_tvl a

JOIN

price_tab b ON a.date = b.date

WHERE

a.chain ILIKE '%klaytn%'

ORDER BY

date DESC

SELECT

date AS "Date",

current_tvl AS "TVL in $USD",

current_tvl_kaia AS "TVL in $KAIA",