WITH daily_prices AS (

SELECT

token_address,

symbol,

blockchain,

DATE_TRUNC('day', hour) AS day,

AVG(price) AS daily_avg_price

FROM

crosschain.price.ez_prices_hourly

WHERE

blockchain = 'ethereum' -- Replace with the specific blockchain

AND token_address IN ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0xdac17f958d2ee523a2206206994597c13d831ec7') -- Replace with the specific token addresses

GROUP BY

token_address, symbol, blockchain, DATE_TRUNC('day', hour)

),

price_volatility AS (

SELECT

token_address,

symbol,

blockchain,

day,

STDDEV(daily_avg_price) AS daily_price_volatility

FROM

daily_prices

GROUP BY

token_address, symbol, blockchain, day

SELECT

lp_actions.pool_address,

lp_actions.token0_symbol AS asset0_symbol,

lp_actions.token1_symbol AS asset1_symbol,

pv0.day,

pv0.daily_price_volatility AS price_volatility0,