select hour,

avg (price) as savaxprice

from avalanche.price.ez_prices_hourly

where token_address = '0x2b2c81e08f1af8835a78bb2a90ae924ace0ea4be'

and hour >= '2025-03-01'

group by 1)

select case when platform ilike '%trader-joe%' then 'Trader Joe'

when platform ilike '%uniswap%' then 'Uniswap'

when platform ilike '%pharaoh%' then 'Pharaoh' else initcap(platform) end as dex,

symbol_in || ' => ' || symbol_out as Trading_Pair,

count (distinct tx_hash) as TX_Count,

count (distinct origin_from_address) as Traders_Count,

sum (case when token_in = '0x3fe4902b275caf603c46c81f3d921bb8515b5bc0' then amount_in

when token_out = '0x3fe4902b275caf603c46c81f3d921bb8515b5bc0' then amount_out end) as Total_Trading_Volume,

sum (case when amount_in_usd > 0 or amount_out_usd > 0 then coalesce(amount_in_usd,amount_out_usd,0) when token_in = '0x2b2c81e08f1af8835a78bb2a90ae924ace0ea4be' then amount_in*savaxprice when token_out = '0x2b2c81e08f1af8835a78bb2a90ae924ace0ea4be' then amount_out*savaxprice end) as Total_USD_Trading_Volume,

avg (case when token_in = '0x3fe4902b275caf603c46c81f3d921bb8515b5bc0' then amount_in

when token_out = '0x3fe4902b275caf603c46c81f3d921bb8515b5bc0' then amount_out end) as Average_Trading_Volume,

avg (case when amount_in_usd > 0 or amount_out_usd > 0 then coalesce(amount_in_usd,amount_out_usd,0) when token_in = '0x2b2c81e08f1af8835a78bb2a90ae924ace0ea4be' then amount_in*savaxprice when token_out = '0x2b2c81e08f1af8835a78bb2a90ae924ace0ea4be' then amount_out*savaxprice end) as Average_USD_Trading_Volume,

from avalanche.defi.ez_dex_swaps t1 left outer join savaxpricet t3 on date_trunc (hour,t1.block_Timestamp) = t3.hour

where token_in in ('0x3fe4902b275caf603c46c81f3d921bb8515b5bc0')

or token_out = '0x3fe4902b275caf603c46c81f3d921bb8515b5bc0'

group by 1,2