select

HOUR :: Date as day,

avg(PRICE) as price,

token_address

from

ethereum.price.ez_hourly_token_prices

group by

1,

token_address

),

mid as (

select

a.*,

case

when amount_in_usd is null then amount_in * b.price

else amount_in_usd

end as sell_usd,

case

when amount_out_usd is null then amount_out * c.price

else amount_out_usd

end as buy_usd

from

ethereum.defi.ez_dex_swaps a

left outer join price b on a.BLOCK_TIMESTAMP :: date = b.day

and a.token_in = b.token_address

left outer join price c on a.BLOCK_TIMESTAMP :: date = c.day

and a.token_out = c.token_address

),

base as (

select

'Pepe' as token,

-- concat (SYMBOL_IN,'/',SYMBOL_OUT) as pair ,

-- PLATFORM,

count(distinct SENDER) as trader_count,

count(distinct TX_HASH) as tx_count,