contract_address,

ifnull(name,contract_address) as contract,

case when contract_address = '0x4d73adb72bc3dd368966edd0f0b2148401a178e2' then 'Layer Zero'

when contract_address = '0xfae3f424a0a47706811521e3ee268f00cfb5c45e' then 'Uniswap Pool'

when contract_address = '0xd56e4eab23cb81f43168f9f45211eb027b9ac7cc' then 'Layer Zero'

when contract_address = '0x1a1ec25dc08e98e5e93f1104b5e5cdd298707d31' then 'Metamask Router'

when contract_address = '0x9d1b1669c73b033dfe47ae5a0164ab96df25b944' then 'Stargate Finance'

when contract_address = '0x398baa6ffc99126671ab6be565856105a6118a40' then 'Hyperspace'

when contract_address = '0xc605c2cf66ee98ea925b1bb4fea584b71c00cc4c' then 'Arena'

when contract_address = '0x88de50b233052e4fb783d4f6db78cc34fea3e9fc' then 'Odos Router'

when contract_address = '0x794a61358d6845594f94dc1db02a252b5b4814ad' then 'AAVE V3' else contract end as contract_name,

count(DISTINCT a.block_timestamp::date) as "Active Days",

count(DISTINCT origin_from_address) as "Users",

count(DISTINCT a.tx_hash) as "Transactions",

"Users"/"Transactions" as "Avg Transaction Per User",

sum(tx_fee) as "Fee (AVAX)",

avg(tx_fee) as "Avg Fee (AVAX)",

median(tx_fee) as "Median Fee (AVAX)",

max(tx_fee) as "Max Fee (AVAX)"

from avalanche.core.fact_event_logs a join avalanche.core.fact_transactions b on a.tx_hash = b.tx_hash

left outer join crosschain.core.dim_contracts c on a.contract_address = c.address

where a.block_timestamp::date >= current_date - 30

group by 1,2,3

order by 5 desc

limit 10