SELECT

POOL_NAME,

COUNT(DISTINCT from_address) AS swappers,

COUNT(DISTINCT tx_id) AS transactions,

SUM(CASE

WHEN FROM_AMOUNT_USD IS NOT NULL THEN FROM_AMOUNT_USD

ELSE TO_AMOUNT_USD

END) AS swap_volume_usd,

RANK() OVER (ORDER BY SUM(CASE

WHEN FROM_AMOUNT_USD IS NOT NULL THEN FROM_AMOUNT_USD

ELSE TO_AMOUNT_USD

END) DESC) AS rank

FROM maya.defi.fact_swaps

WHERE BLOCK_TIMESTAMP > CURRENT_DATE - 30

GROUP BY 1

SELECT

CASE

WHEN rank <= 7 THEN POOL_NAME

ELSE 'Other'

END AS pool_category,

SUM(swappers) AS total_swappers,

SUM(transactions) AS total_transactions,

SUM(swap_volume_usd) AS total_swap_volume_usd

FROM ranked_pools

GROUP BY 1

ORDER BY total_swap_volume_usd DESC;