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 date_trunc (day,block_timestamp) as date,

case when token_in = '0x3fe4902b275caf603c46c81f3d921bb8515b5bc0' then 'Swap From JACK (SELL)'

when token_out = '0x3fe4902b275caf603c46c81f3d921bb8515b5bc0' then 'Swap To JACK (BUY)' end as Trading_Type,

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 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 USD_Trading_Volume,

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

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

sum (tx_count) over (partition by Trading_Type order by date) as Cumulative_TX_Count,

sum (Trading_Volume) over (partition by Trading_Type order by date) as Cumulative_Trading_Volume,

sum (USD_Trading_Volume) over (partition by Trading_Type order by date) as Cumulative_USD_Trading_Volume,

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

where token_in in ('0x3fe4902b275caf603c46c81f3d921bb8515b5bc0')

or token_out = '0x3fe4902b275caf603c46c81f3d921bb8515b5bc0'

group by 1,2

order by 1 desc