1WITH volume_analysis AS (
2    SELECT 
3        instrument,
4        datetime,
5        close,
6        volume,
7        AVG(volume) OVER (PARTITION BY instrument ORDER BY datetime ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS avg_volume_30d,
8        volume / AVG(volume) OVER (PARTITION BY instrument ORDER BY datetime ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS volume_ratio
9    FROM "@coindesk.indices.coindesk_spot_price"
10    WHERE instrument IN ('BTC-USD', 'ETH-USD', 'SOL-USD')
11      AND datetime >= '2024-01-01'
12),
13monthly_stats AS (
14    SELECT 
15        DATE_TRUNC('month', datetime) AS month,
16        instrument,
17        COUNT(CASE WHEN volume_ratio > 2 THEN 1 END) AS spike_days,
18        ROUND(AVG(volume_ratio), 2) AS avg_ratio,
19        ROUND(MAX(volume_ratio), 2) AS max_ratio
20    FROM volume_analysis
21    WHERE volume_ratio IS NOT NULL
22    GROUP BY DATE_TRUNC('month', datetime), instrument
23)
24SELECT 
25    month,
26    MAX(CASE WHEN instrument = 'BTC-USD' THEN spike_days END) AS btc_spike_days,
27    MAX(CASE WHEN instrument = 'ETH-USD' THEN spike_days END) AS eth_spike_days,
28    MAX(CASE WHEN instrument = 'SOL-USD' THEN spike_days END) AS sol_spike_days,
29    MAX(CASE WHEN instrument = 'BTC-USD' THEN avg_ratio END) AS btc_avg_volume_ratio,
30    MAX(CASE WHEN instrument = 'ETH-USD' THEN avg_ratio END) AS eth_avg_volume_ratio,
31    MAX(CASE WHEN instrument = 'SOL-USD' THEN avg_ratio END) AS sol_avg_volume_ratio,
32    MAX(CASE WHEN instrument = 'BTC-USD' THEN max_ratio END) AS btc_max_spike,
33    MAX(CASE WHEN instrument = 'ETH-USD' THEN max_ratio END) AS eth_max_spike,
34    MAX(CASE WHEN instrument = 'SOL-USD' THEN max_ratio END) AS sol_max_spike
35FROM monthly_stats
36GROUP BY month
37ORDER BY month DESC