origin_from_address,

CASE

WHEN platform IN ('kyberswap-v1', 'kyberswap-v2') THEN 'Kyberswap'

WHEN platform IN ('pharaoh-v1', 'pharaoh-v2') THEN 'Pharaoh'

WHEN platform IN ('trader-joe-v1', 'trader-joe-v2') THEN 'Trader Joe'

WHEN platform IN ('uniswap-v2', 'uniswap-v3') THEN 'Uniswap'

ELSE platform

END as platforms,

count(DISTINCT tx_hash) as tx_count,

count(DISTINCT pool_name) as pool_count,

sum(amount_in_usd) as total_vol,

FROM

avalanche.defi.ez_dex_swaps

WHERE

block_timestamp::date >= current_date - interval '30 days'

GROUP BY

origin_from_address, platforms

select

platforms,

avg(tx_count) as avg_tx_count_per_user,

avg(total_vol) as avg_tot_vol_per_user,

avg(pool_count) as avg_pairs_per_user

from

users

group by

platforms

order by

platforms asc