select

hour,

symbol,

token_address,

price

from ethereum_core.fact_hourly_token_prices

where symbol in ('TUSD', 'USDN', 'FEI', 'GUSD')

and hour >= getdate() - interval'120 hours'

),

yearly_avg as (

select

symbol,

token_address,

avg(price) as avg_price

from ethereum_core.fact_hourly_token_prices

where symbol in (select symbol from token_price)

and hour >= getdate() - interval'365 days'

group by 1, 2

),

trading_volume as (

select

date_trunc('hour', block_timestamp) as time,

symbol,

sum(amount) as volume

from (

select

block_timestamp,

symbol_in as symbol,

amount_in_usd as amount

from ethereum_core.ez_dex_swaps

where block_timestamp >= getdate() - interval'120 hours'

and symbol_in in (select symbol from token_price)

union all

select

block_timestamp,