1WITH base_prices AS (
2    SELECT 
3        instrument,
4        close AS base_price
5    FROM "@coindesk.indices.coindesk_spot_price"
6    WHERE datetime = '2022-01-01'
7      AND instrument IN ('BTC-USD', 'ETH-USD', 'SOL-USD', 'XRP-USD', 'ADA-USD', 'AVAX-USD', 'MATIC-USD', 'DOT-USD')
8),
9latest_prices AS (
10    SELECT 
11        instrument,
12        close AS current_price,
13        datetime
14    FROM "@coindesk.indices.coindesk_spot_price"
15    WHERE datetime = (SELECT MAX(datetime) FROM "@coindesk.indices.coindesk_spot_price")
16      AND instrument IN ('BTC-USD', 'ETH-USD', 'SOL-USD', 'XRP-USD', 'ADA-USD', 'AVAX-USD', 'MATIC-USD', 'DOT-USD')
17)
18SELECT 
19    b.instrument,
20    ROUND(b.base_price, 2) AS price_jan_2022,
21    ROUND(l.current_price, 2) AS price_now,
22    ROUND((l.current_price / b.base_price - 1) * 100, 2) AS total_return_pct,
23    ROUND(1000 * (l.current_price / b.base_price), 2) AS value_of_1000_invested
24FROM base_prices b
25JOIN latest_prices l ON b.instrument = l.instrument
26ORDER BY total_return_pct DESC