with arenapricet as (

select date_trunc (hour,block_timestamp) as hour1,

avg (amount_in_usd/amount_out) as arenaprice

from avalanche.defi.ez_dex_swaps

where symbol_in = 'WAVAX'

and (symbol_out = 'ARENA' or token_out = '0xb8d7710f7d8349a506b75dd184f05777c82dad0c')

group by 1

order by 1 desc)

select date_Trunc (hour,t1.block_timestamp) as date,

count (distinct to_address) as Airdrop_Claimers,

sum (amount) as Claimed_Amount,

avg (amount) as Average_Amount,

median (amount) as Median_Amount,

min (amount) as Min_Amount,

max (amount) as Maximum_Amount,

sum (amount*arenaprice) as Claimed_Amount_USD,

avg (amount*arenaprice) as Average_Amount_usd,

median (amount*arenaprice) as Median_Amount_usd,

min (amount*arenaprice) as Min_Amount_usd,

max (amount*arenaprice) as Maximum_Amount_usd,

sum (Claimed_Amount) over (order by date) as Total_Claimed,

sum (Airdrop_Claimers) over (order by date) as Total_Claimers

from avalanche.core.ez_token_transfers t1 left outer join arenapricet t2 on date_trunc (hour,t1.block_timestamp) = t2.hour1

where contract_address = '0xb8d7710f7d8349a506b75dd184f05777c82dad0c'

and from_address = '0x30f587903ef022774f5e35151e0e73c196bd755d'

and origin_function_signature = '0x3b439351'

and block_timestamp >= '2025-03-01 18:30:00.000'

group by 1

order by 1 desc