select distinct pool_name, sum(AMOUNT1_USD) as volume

from ethereum.uniswapv3.ez_swaps

group by 1 having volume is not null order by 2 desc limit 10

),

price_changes AS (

SELECT

DATE_TRUNC('week', BLOCK_TIMESTAMP) AS day,

POOL_NAME,

TOKEN0_SYMBOL,

TOKEN1_SYMBOL,

PRICE_0_1 AS price,

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

FROM ethereum.uniswapv3.ez_swaps

WHERE POOL_NAME in (select distinct pool_name from top10)

AND BLOCK_TIMESTAMP BETWEEN '{{start_date}}' AND '{{end_date}}'

),

daily_volatility AS (

SELECT

day,

POOL_NAME,

TOKEN0_SYMBOL,

TOKEN1_SYMBOL,

STDDEV(price - previous_price) AS daily_volatility

FROM price_changes

WHERE previous_price IS NOT NULL

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

SELECT

POOL_NAME,

TOKEN0_SYMBOL,

TOKEN1_SYMBOL,

sum(daily_volatility) as cum_volatility,

-- LVR continuous formula: σ² / 8

POWER(cum_volatility, 2) / 8 AS total_LVR_continuous