SELECT

dlp.pool_address,

dlp.pool_name,

dlp.platform,

COUNT(DISTINCT ds.tx_hash) AS num_swaps,

SUM(COALESCE(ds.amount_in_usd, 0)) AS total_volume_usd

FROM kaia.defi.dim_dex_liquidity_pools dlp

JOIN kaia.defi.ez_dex_swaps ds ON dlp.pool_address = ds.contract_address

WHERE ds.block_timestamp >= '2024-01-01'

GROUP BY dlp.pool_address, dlp.pool_name, dlp.platform

),

ranked_pools AS (

SELECT

*,

RANK() OVER (PARTITION BY platform ORDER BY total_volume_usd DESC) AS volume_rank,

RANK() OVER (PARTITION BY platform ORDER BY num_swaps DESC) AS swap_rank

FROM pool_metrics

pool_address,

pool_name,

platform,

num_swaps,

total_volume_usd,

volume_rank AS volume_rank_within_platform,

swap_rank AS swap_rank_within_platform

FROM ranked_pools

WHERE volume_rank <= 10 OR swap_rank <= 10

ORDER BY platform, total_volume_usd DESC, num_swaps DESC;