select hour as price_hour,

token_address as price_token_address,

symbol as price_symbol,

avg(price) as price

from ethereum.core.fact_hourly_token_prices

group by 1,2,3

, top_buy as (

select date_trunc('month', block_timestamp) as month,

'BUY' as type,

token_out as token_address,

price_symbol,

sum(amount_out) as total_amount,

sum(amount_out*price) as total_amount_usd,

row_number () over (partition by month order by total_amount_usd desc) rn

FROM ethereum.core.ez_dex_swaps

join price on (date_trunc('hour', block_timestamp) = price_hour

and token_out = price_token_address)

where platform ilike '%uniswap%'

and amount_out*price < 10000000 -- Remove overflowing outlier

group by 1,2,3,4

qualify rn <= 100

, top_sell as (

select date_trunc('month', block_timestamp) as month,

'SELL' as type,

token_in as token_address,

price_symbol as symbol,

sum(amount_in) as total_amount,

sum(amount_in*price) as total_amount_usd,

row_number () over (partition by month order by total_amount_usd desc) rn

FROM ethereum.core.ez_dex_swaps

join price on (date_trunc('hour', block_timestamp) = price_hour

and token_in = price_token_address)