1WITH daily_returns AS (
2    SELECT 
3        instrument,
4        datetime,
5        close,
6        ((close - LAG(close) OVER (PARTITION BY instrument ORDER BY datetime)) / 
7         LAG(close) OVER (PARTITION BY instrument ORDER BY datetime) * 100) AS daily_return
8    FROM "@coindesk.indices.coindesk_spot_price"
9    WHERE instrument IN ('BTC-USD', 'ETH-USD', 'SOL-USD', 'XRP-USD', 'ADA-USD')
10      AND datetime >= '2024-06-01'
11),
12rolling_volatility AS (
13    SELECT 
14        instrument,
15        datetime,
16        daily_return,
17        STDDEV(daily_return) OVER (
18            PARTITION BY instrument 
19            ORDER BY datetime 
20            ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
21        ) AS volatility_30d
22    FROM daily_returns
23    WHERE daily_return IS NOT NULL
24)
25SELECT 
26    datetime,
27    ROUND(MAX(CASE WHEN instrument = 'BTC-USD' THEN volatility_30d END), 2) AS btc_volatility,
28    ROUND(MAX(CASE WHEN instrument = 'ETH-USD' THEN volatility_30d END), 2) AS eth_volatility,
29    ROUND(MAX(CASE WHEN instrument = 'SOL-USD' THEN volatility_30d END), 2) AS sol_volatility,
30    ROUND(MAX(CASE WHEN instrument = 'XRP-USD' THEN volatility_30d END), 2) AS xrp_volatility,
31    ROUND(MAX(CASE WHEN instrument = 'ADA-USD' THEN volatility_30d END), 2) AS ada_volatility
32FROM rolling_volatility
33WHERE datetime >= '2023-01-01'
34GROUP BY datetime
35ORDER BY datetime DESC
36LIMIT 500