select

case when contract_address = '0xe30fedd158a2e3b13e9badaeabafc5516e95e8c7' then 'WSEI'

when contract_address = '0xc18b6a15fb0ceaf5eb18696eefcb5bc7b9107149' then 'POPO'

when contract_address = '0xa2aee4f82023c6614f3ab40041c3b434ff97b439' then 'POPO/WSEI Pool'

when contract_address = '0x00005ea00ac477b1030ce78506496e8c2de24bf5' then 'Seadrop'

when contract_address = '0x391dd87d465b55e295bad33fbc4231d2b6d35327' then 'FACES'

when contract_address = '0x17fe1101af2dab1af0317d1ad1bd66cd06da4b5c' then 'SeiPepe/WSEI Pool'

when contract_address = '0xc6bc81a0e287cc8103cc002147a9d76cae4cd6e5' then 'Seiballz'

when contract_address = '0x2132124986c25525b1c55f9684f1af5f69ea13c0' then 'Ballz/WSEI pool'

when contract_address = '0x51752d6f4f9191c819c1e4c4c395196ac35d3304' then 'SeiPepe/WSEI Pool'

when contract_address = '0xb56636bb80178ec767fcba4c95634e4cbf0753b2' then 'SeiPepe'

when contract_address = '0x5f0e07dfee5832faa00c63f2d33a0d79150e8598' then 'Seiyan'

when contract_address = '0x99b85e9dfffed176e46a3be009ab9f9fe6ae59ed' then 'OpenSei'

when contract_address = '0xae7129065d88b731e84524f7cad2239a50f65f5e' then 'DragonSwap'

when contract_address = '0x71f6b49ae1558357bbb5a6074f1143c46cbca03d' then 'DragonSwap'

when contract_address = '0x029dab7d8270ab5120bee56f7d8214e9db4f2389' then 'Unknown'

when contract_address = '0x8ba502d9243fa5e44678044098be9782cebafda5' then 'Seiyan/WSEI Pool'

when contract_address = '0x3894085ef7ff0f0aedf52e2a2704928d1ec074f1' then 'USDC/USDT Pool'

when contract_address = '0xc75c669a62a7ece0c8d37904b747970467432ad3' then 'USDC/WSEI Pool'

when contract_address = lower('0x7E5ED78FE5D895BD90522FC24C6FF65F842Eeed3') then 'Seifam SBT' else name end as type,

contract_address,

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 (Sei)",

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

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

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

from sei.core_evm.fact_event_logs a join sei.core_evm.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 >= '2024-05-27'

and type is not null

group by 1,2