SELECT

DATE_TRUNC('day', block_timestamp) AS trading_day,

platform,

COUNT(DISTINCT swapper) AS active_traders,

COUNT(*) AS swap_count,

SUM(amount_in) AS volume_in,

SUM(amount_out) AS volume_out,

AVG(amount_out / NULLIF(amount_in, 0)) AS avg_rate,

COUNT(DISTINCT pool_address) AS pools_used,

COUNT(DISTINCT currency_in || '-' || CURRENCY_out) AS unique_pairs

FROM sei.defi.fact_dex_swaps

WHERE block_timestamp >= '2024-09-01' AND block_timestamp <= CURRENT_DATE()

AND tx_succeeded = TRUE

GROUP BY 1, 2

),

platform_metrics AS (

SELECT

trading_day,

platform,

active_traders,

swap_count,

volume_in,

volume_out,

avg_rate,

pools_used,

unique_pairs,

SUM(swap_count) OVER (PARTITION BY platform ORDER BY trading_day) AS cumulative_swaps,

AVG(swap_count) OVER (PARTITION BY platform ORDER BY trading_day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_weekly_avg_swaps,

LAG(volume_in, 1) OVER (PARTITION BY platform ORDER BY trading_day) AS prev_day_volume,

RANK() OVER (PARTITION BY trading_day ORDER BY volume_in DESC) AS daily_volume_rank

FROM daily_exchange_metrics

),

overall_metrics AS (

SELECT

trading_day,