1WITH index_returns AS (
2    SELECT 
3        instrument,
4        datetime,
5        close,
6        (close / FIRST_VALUE(close) OVER (PARTITION BY instrument ORDER BY datetime) - 1) * 100 AS cumulative_return,
7        ((close - LAG(close, 30) OVER (PARTITION BY instrument ORDER BY datetime)) / 
8         LAG(close, 30) OVER (PARTITION BY instrument ORDER BY datetime) * 100) AS return_30d
9    FROM "@coindesk.indices.multi_assets_indices"
10)
11SELECT 
12    datetime,
13    ROUND(MAX(CASE WHEN instrument = 'CD5-USD' THEN cumulative_return END), 2) AS cd5_total_return,
14    ROUND(MAX(CASE WHEN instrument = 'CD20SPOT-USD' THEN cumulative_return END), 2) AS cd20_total_return,
15    ROUND(MAX(CASE WHEN instrument = 'CD80-USD' THEN cumulative_return END), 2) AS cd80_total_return,
16    ROUND(MAX(CASE WHEN instrument = 'CD100-USD' THEN cumulative_return END), 2) AS cd100_total_return,
17    ROUND(MAX(CASE WHEN instrument = 'CD5-USD' THEN return_30d END), 2) AS cd5_30d_return,
18    ROUND(MAX(CASE WHEN instrument = 'CD20SPOT-USD' THEN return_30d END), 2) AS cd20_30d_return,
19    ROUND(MAX(CASE WHEN instrument = 'CD80-USD' THEN return_30d END), 2) AS cd80_30d_return
20FROM index_returns
21WHERE datetime >= '2022-10-04'
22GROUP BY datetime
23ORDER BY datetime DESC
24LIMIT 100