3,256 answered market questions

WITH
    daily AS
    (
        SELECT
            t.date                                                   AS d,
            toFloat64(t.yield_10_year) - toFloat64(e.market_10_year) AS real_10y,
            toFloat64(g.close)                                       AS gld_close
        FROM global_markets.treasury_yields AS t
        INNER JOIN global_markets.inflation_expectations AS e ON e.date = t.date
        INNER JOIN
        (
            SELECT
                date,
                max(close) AS close
            FROM global_markets.stocks_daily_aggs
            WHERE ticker = 'GLD'
              AND date >= '2005-01-01'
            GROUP BY date
        ) AS g ON g.date = t.date
        WHERE t.date >= '2005-01-01'
          AND t.yield_10_year > 0
          AND e.market_10_year > 0
    ),
    changes AS
    (
        SELECT
            d,
            real_10y,
            gld_close,
            real_10y - prev_real       AS real_chg,
            gld_close / prev_close - 1 AS gld_ret
        FROM
        (
            SELECT
                d,
                real_10y,
                gld_close,
                lagInFrame(real_10y)  OVER (ORDER BY d ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_real,
                lagInFrame(gld_close) OVER (ORDER BY d ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_close
            FROM daily
        )
        WHERE prev_close > 0
    ),
    tagged AS
    (
        SELECT
            multiIf(
                d BETWEEN toDate('2008-06-30') AND toDate('2008-12-31'), '2008 credit crisis',
                d BETWEEN toDate('2013-04-30') AND toDate('2013-12-31'), '2013 taper repricing',
                d BETWEEN toDate('2019-12-31') AND toDate('2020-08-31'), '2020 easing cycle',
                d BETWEEN toDate('2021-12-31') AND toDate('2022-10-31'), '2022 hiking cycle',
                d BETWEEN toDate('2024-12-31') AND toDate('2026-09-30'), '2025 to 2026 advance',
                'other')                                                 AS episode,
            multiIf(
                d BETWEEN toDate('2008-06-30') AND toDate('2008-12-31'), 'Jun 2008 to Dec 2008',
                d BETWEEN toDate('2013-04-30') AND toDate('2013-12-31'), 'Apr 2013 to Dec 2013',
                d BETWEEN toDate('2019-12-31') AND toDate('2020-08-31'), 'Dec 2019 to Aug 2020',
                d BETWEEN toDate('2021-12-31') AND toDate('2022-10-31'), 'Dec 2021 to Oct 2022',
                d BETWEEN toDate('2024-12-31') AND toDate('2026-09-30'), 'Dec 2024 to Sep 2026',
                'other')                                                 AS span_label,
            d,
            real_10y,
            gld_close,
            real_chg,
            gld_ret
        FROM changes
    )
SELECT
    episode,
    span_label,
    round(argMax(real_10y, d) - argMin(real_10y, d), 2)               AS real_yield_delta,
    round(100 * (argMax(gld_close, d) / argMin(gld_close, d) - 1), 1) AS gld_return_pct,
    round(corr(real_chg, gld_ret), 2)                                 AS daily_corr
FROM tagged
WHERE episode != 'other'
GROUP BY episode, span_label
ORDER BY min(d)