SELECT

DATE_TRUNC('hour', block_timestamp) as trade_hour,

platform,

SUM(amount_in_usd) as hourly_volume,

COUNT(*) as num_trades

FROM avalanche.defi.ez_dex_swaps

WHERE block_timestamp >= DATEADD(month, -2, CURRENT_DATE())

GROUP BY 1, 2

),

volatility_analysis AS (

SELECT

trade_hour,

platform,

hourly_volume,

num_trades,

AVG(hourly_volume) OVER (PARTITION BY platform ORDER BY trade_hour

ROWS BETWEEN 24 PRECEDING AND 1 PRECEDING) as avg_24h_volume,

STDDEV(hourly_volume) OVER (PARTITION BY platform ORDER BY trade_hour

ROWS BETWEEN 24 PRECEDING AND 1 PRECEDING) as stddev_24h_volume

FROM hourly_stats

SELECT

trade_hour,

platform,

hourly_volume,

num_trades,

avg_24h_volume,

((hourly_volume - avg_24h_volume) / NULLIF(stddev_24h_volume, 0)) as z_score,

CASE

WHEN ((hourly_volume - avg_24h_volume) / NULLIF(stddev_24h_volume, 0)) > 2 THEN 'High Volatility ⚠️'

WHEN ((hourly_volume - avg_24h_volume) / NULLIF(stddev_24h_volume, 0)) < -2 THEN 'Low Volume Alert 🔻'

ELSE 'Normal Range ✅'

END as volatility_status

FROM volatility_analysis

WHERE stddev_24h_volume > 0