WITH price_changes AS (

SELECT

DATE_TRUNC('week', BLOCK_TIMESTAMP) AS day,

POOL_ADDRESS,

TOKEN0_SYMBOL,

TOKEN1_SYMBOL,

PRICE_0_1 AS price,

LAG(PRICE_0_1, 1) OVER (PARTITION BY POOL_ADDRESS ORDER BY BLOCK_TIMESTAMP) AS previous_price

FROM ethereum.uniswapv3.ez_swaps

WHERE POOL_NAME = '{{pool_name}}'

),

daily_volatility AS (

SELECT

day,

POOL_ADDRESS,

TOKEN0_SYMBOL,

TOKEN1_SYMBOL,

STDDEV(price - previous_price) AS daily_volatility

FROM price_changes

WHERE previous_price IS NOT NULL

GROUP BY day, POOL_ADDRESS, TOKEN0_SYMBOL, TOKEN1_SYMBOL having daily_volatility<100

SELECT

day,

POOL_ADDRESS,

TOKEN0_SYMBOL,

TOKEN1_SYMBOL,

daily_volatility,

-- LVR continuous formula: σ² / 8

POWER(daily_volatility, 2) / 8 AS daily_LVR_continuous,

avg(daily_LVR_continuous) over (order by day) as avg_LVR_continuous,

sum(daily_LVR_continuous) over (order by day) as cum_LVR_continuous

FROM daily_volatility

ORDER BY day desc