WITH b0 AS (

SELECT REPLACE(SPLIT(SPLIT(t.pool_name, '.')[0], '/')[0], 'GAIA', 'ATOM') AS chain

, SUM(swap_volume_rune_usd) AS swap_volume_usd

FROM thorchain.core.fact_daily_pool_stats t

WHERE day >= DATEADD('month', -18, CURRENT_DATE)

GROUP BY 1

SELECT *

, to_varchar(swap_volume_usd, '999,999,999,999') AS swap_volume_usd_label

, SUM(swap_volume_usd) OVER () AS total_swap_volume_usd

, to_varchar(total_swap_volume_usd, '999,999,999,999') AS total_swap_volume_usd_label

, ROW_NUMBER() OVER (ORDER BY swap_volume_usd DESC) AS swap_volume_usd_rank

, to_varchar(swap_volume_usd_rank, '99') AS rank_label

, CONCAT(rank_label, ': ', chain) AS label

FROM b0

ORDER BY swap_volume_usd DESC