SPY versus its hike-day close, week by week, three cycles aligned

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH
    px AS
    (
        SELECT
            date,
            toFloat64(close) AS px_close,
            multiIf(date <= '2005-06-30', toDate('2004-06-30'),
                    date <= '2016-12-16', toDate('2015-12-16'),
                                          toDate('2022-03-16')) AS hike_date
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND ((date >= '2004-06-30' AND date <= '2005-06-30')
            OR (date >= '2015-12-16' AND date <= '2016-12-16')
            OR (date >= '2022-03-16' AND date <= '2023-03-16'))
    ),
    base AS
    (
        SELECT hike_date, argMin(px_close, date) AS hike_close
        FROM px
        GROUP BY hike_date
    ),
    weekly AS
    (
        SELECT
            p.hike_date                                          AS hk,
            intDiv(dateDiff('day', p.hike_date, p.date) + 6, 7)  AS wk,
            argMax(p.px_close, p.date) / any(b.hike_close)       AS ratio
        FROM px AS p
        INNER JOIN base AS b ON p.hike_date = b.hike_date
        GROUP BY hk, wk
    )
SELECT
    wk                                                           AS wk_after_hike,
    round(100 * (maxIf(ratio, toYear(hk) = 2004) - 1), 1)        AS cycle_2004_pct,
    round(100 * (maxIf(ratio, toYear(hk) = 2015) - 1), 1)        AS cycle_2015_pct,
    round(100 * (maxIf(ratio, toYear(hk) = 2022) - 1), 1)        AS cycle_2022_pct
FROM weekly
WHERE wk <= 52
GROUP BY wk
ORDER BY wk