SELECT date_trunc('day', hour) as date,

avg(price) as OP_price,

lag(OP_price) ignore nulls over(order by date asc) as lag_OP_price,

((OP_price-lag_OP_price)/ lag_OP_price)*100 as OP_volatility

FROM optimism.core.fact_hourly_token_prices

WHERE token_address = '0x4200000000000000000000000000000000000042'

GROUP BY 1

),

eth_price as (

select

date_trunc('day', hour) as date,

avg(price) as eth_price,

lag(eth_price) ignore nulls over(order by date asc) as lag_eth_price,

((eth_price-lag_eth_price)/ lag_eth_price)*100 as ETH_volatility

from ethereum.core.fact_hourly_token_prices

where symbol = 'WETH'

and date >='2022-11-01'

group by date

order by date

),

main1 as (

select

op.date,

op.OP_price,

eth.eth_price,

op_volatility,

ETH_volatility

from op op

join eth_price eth

on op.date=eth.date

select *