1WITH all_dates AS (
2    SELECT DISTINCT datetime
3    FROM "@coindesk.indices.rates"
4    WHERE instrument IN ('CESR-ETH', 'CDORAUSC', 'CDORAUST')
5      AND datetime >= '2024-06-10'
6),
7pivoted AS (
8    SELECT 
9        datetime,
10        MAX(CASE WHEN instrument = 'CESR-ETH' THEN close * 100 END) AS cesr_eth_rate,
11        MAX(CASE WHEN instrument = 'CDORAUSC' THEN close * 100 END) AS cdor_usdc_rate,
12        MAX(CASE WHEN instrument = 'CDORAUST' THEN close * 100 END) AS cdor_usdt_rate
13    FROM "@coindesk.indices.rates"
14    WHERE instrument IN ('CESR-ETH', 'CDORAUSC', 'CDORAUST')
15      AND datetime >= '2024-06-10'
16    GROUP BY datetime
17)
18SELECT 
19    datetime,
20    ROUND(LAST_VALUE(cesr_eth_rate IGNORE NULLS) OVER (ORDER BY datetime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS cesr_eth_rate,
21    ROUND(LAST_VALUE(cdor_usdc_rate IGNORE NULLS) OVER (ORDER BY datetime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS cdor_usdc_rate,
22    ROUND(LAST_VALUE(cdor_usdt_rate IGNORE NULLS) OVER (ORDER BY datetime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS cdor_usdt_rate
23FROM pivoted
24ORDER BY datetime DESC