a.hour::date as date,

case

when date < '2022-11-08' then 'Before'

when date > '2022-11-08' then 'After'

end as collapse,

avg (a.price) as ETH_Price

from ethereum.core.fact_hourly_token_prices a join ethereum.core.fact_transactions b on a.HOUR::date=b.BLOCK_TIMESTAMP::date

where a.symbol = 'WETH'

and date>='2022-11-01'

group by 1,2

order by 1