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 origin_from_address) as user_count,

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

platforms

),

totals AS (

SELECT

SUM(user_count) AS total_users,

SUM(tx_count) AS total_tx_count,

SUM(total_vol) AS total_volume,

FROM

platform_stats

ps.platforms,

ps.user_count,

ROUND(100.0 * ps.user_count / t.total_users, 2) AS user_percentage,

ps.tx_count,

ROUND(100.0 * ps.tx_count / t.total_tx_count, 2) AS tx_percentage,

ps.total_vol,