SELECT

DATE_TRUNC('day', block_timestamp) as trade_date,

platform,

COUNT(DISTINCT tx_hash) as num_swaps,

COUNT(DISTINCT origin_from_address) as unique_traders,

SUM(COALESCE(NULLIF(amount_in_usd, 0), amount_out_usd)) as daily_volume,

AVG(COALESCE(NULLIF(amount_in_usd, 0), amount_out_usd)) as avg_swap_size,

MAX(COALESCE(NULLIF(amount_in_usd, 0), amount_out_usd)) as largest_swap,

COUNT(DISTINCT token_in) + COUNT(DISTINCT token_out) as unique_tokens_traded,

SUM(CASE WHEN amount_in_usd > 100000 THEN 1 ELSE 0 END) as large_swaps_count

FROM blast.defi.ez_dex_swaps

WHERE block_timestamp >= '2024-01-01'

GROUP BY 1, 2

),

platform_daily_metrics AS (

SELECT

trade_date,

platform,

num_swaps,

unique_traders,

daily_volume,

avg_swap_size,

largest_swap,

unique_tokens_traded,

large_swaps_count,

SUM(daily_volume) OVER (PARTITION BY trade_date) as total_daily_volume,

AVG(daily_volume) OVER (

PARTITION BY platform

ORDER BY trade_date

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

) as rolling_7d_avg_volume,

LAG(daily_volume) OVER (PARTITION BY platform ORDER BY trade_date) as prev_day_volume,

ROW_NUMBER() OVER (PARTITION BY trade_date ORDER BY daily_volume DESC) as volume_rank

FROM daily_volume

),