select hour::date as date,

symbol,

decimals,

avg (price) as avg_price

from ethereum.core.fact_hourly_token_prices

where (symbol in ('USDC','MIM','LINK','USDT','WETH','WBTC','DAI','FRAX','WAVAX')

or token_address in ('0x1f9840a85d5af5bf1d1762f925bdaddc4201f984')) --UNI

group by 1,2,3),

avalanche_gmx as (select

block_timestamp,

tx_hash,

origin_from_address,

event_inputs:value as volume,

case when contract_address in ('0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e','0xa7d7079b0fead91f3e65f86e8915cb59c1a4c664') then 'USDC'

when contract_address = '0x49d5c2bdffac6ce2bfdb6640f4f80f226bc10bab' then 'WETH'

when contract_address in ('0x152b9d0fdc40c096757f570a51e494bd4b943e50','0x50b7545627a5162f82a992c33b87adc75187b218') then 'WBTC'

when contract_address = '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7' then 'WAVAX'

else contract_address end as Token_Symbol

from avalanche.core.fact_event_logs

where origin_to_address in ('0xfff6d276bc37c61a23f06410dce4a400f66420f8')

and tx_status= 'SUCCESS'

and event_name = 'Transfer'

and event_inputs:value is not null),

final as (

select t1.*,

(t1.volume / pow (10,t2.decimals)) * t2.avg_price as volume_usd

from avalanche_gmx t1 join price t2 on t1.block_timestamp::date = t2.date and t1.Token_Symbol = t2.symbol

where volume_usd > 1 )

count (distinct tx_hash) as TX_Count,

count (distinct origin_from_address) as Users_Count,

sum (volume_usd) as Total_Volume,