SELECT

DATE_TRUNC('day', block_timestamp) AS tx_date,

CASE WHEN block_timestamp < '2024-12-16 17:00:00.000' THEN 'Pre Upgrade'

ELSE 'Post Upgrade' END as upgrade_status,

SYMBOL_OUT,

SUM(CASE WHEN AMOUNT_IN_USD IS NULL THEN AMOUNT_OUT_USD ELSE AMOUNT_IN_USD END) AS total_volume,

COUNT(DISTINCT tx_hash) AS tx_count

FROM avalanche.defi.ez_dex_swaps

WHERE block_timestamp >= CURRENT_DATE - INTERVAL '180 days'

AND AMOUNT_IN_USD IS NOT NULL

GROUP BY 1, 2, 3

SELECT upgrade_status,

SYMBOL_OUT,

total_volume,

tx_count,

LAG(total_volume) OVER (PARTITION BY SYMBOL_OUT ORDER BY tx_date) AS prev_day_volume,

ROUND((total_volume - LAG(total_volume) OVER (PARTITION BY SYMBOL_OUT ORDER BY tx_date)) * 100.0 / NULLIF(LAG(total_volume) OVER (PARTITION BY SYMBOL_OUT ORDER BY tx_date), 0), 2) AS volume_growth_pct

FROM token_flows

ORDER BY volume_growth_pct DESC

LIMIT 20;