1WITH
2  daily_data AS (
3    SELECT
4      DATE(date) AS trading_date,
5      symbol,
6      close AS closing_price
7    FROM
8      "@yahoo.finance.daily_prices"
9    WHERE
10      symbol IN ('IBIT', 'GLD', 'SPY')
11      AND date >= '2025-11-01'
12  ),
13  first_day AS (
14    SELECT
15      symbol,
16      closing_price AS opening_price
17    FROM
18      daily_data
19    WHERE
20      trading_date = (
21        SELECT
22          MIN(trading_date)
23        FROM
24          daily_data
25      )
26  ),
27  pivot_data AS (
28    SELECT
29      d.trading_date,
30      MAX(
31        CASE
32          WHEN d.symbol = 'IBIT' THEN d.closing_price
33        END
34      ) AS ibit_price,
35      MAX(
36        CASE
37          WHEN d.symbol = 'GLD' THEN d.closing_price
38        END
39      ) AS gld_price,
40      MAX(
41        CASE
42          WHEN d.symbol = 'SPY' THEN d.closing_price
43        END
44      ) AS spy_price,
45      MAX(
46        CASE
47          WHEN d.symbol = 'IBIT' THEN (
48            SELECT
49              opening_price
50            FROM
51              first_day
52            WHERE
53              symbol = 'IBIT'
54          )
55        END
56      ) AS ibit_open,
57      MAX(
58        CASE
59          WHEN d.symbol = 'GLD' THEN (
60            SELECT
61              opening_price
62            FROM
63              first_day
64            WHERE
65              symbol = 'GLD'
66          )
67        END
68      ) AS gld_open,
69      MAX(
70        CASE
71          WHEN d.symbol = 'SPY' THEN (
72            SELECT
73              opening_price
74            FROM
75              first_day
76            WHERE
77              symbol = 'SPY'
78          )
79        END
80      ) AS spy_open
81    FROM
82      daily_data d
83    GROUP BY
84      d.trading_date
85  )
86SELECT
87  trading_date,
88  ROUND(ibit_price, 2) AS bitcoin_price,
89  ROUND(gld_price, 2) AS gold_price,
90  ROUND(spy_price, 2) AS sp500_price,
91  ROUND((ibit_price / ibit_open - 1) * 100, 2) AS bitcoin_return_pct,
92  ROUND((gld_price / gld_open - 1) * 100, 2) AS gold_return_pct,
93  ROUND((spy_price / spy_open - 1) * 100, 2) AS sp500_return_pct
94FROM
95  pivot_data
96WHERE
97  ibit_price IS NOT NULL
98  AND gld_price IS NOT NULL
99  AND spy_price IS NOT NULL
100ORDER BY
101  trading_date ASC