The ten worst SPY sessions, 2003-2025, plus where Lehman Monday ranks

WITH daily AS (
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
        argMaxIf(toFloat64(close), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS close_usd
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2003-09-10 00:00:00') AND window_start < toDateTime('2026-01-01 00:00:00')
    GROUP BY et_date
),
changes AS (
    SELECT et_date, close_usd,
           lagInFrame(close_usd) OVER (ORDER BY et_date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
    FROM daily
),
ranked AS (
    SELECT
        row_number() OVER (ORDER BY (close_usd / prev_close - 1) ASC, et_date ASC) AS rank,
        toString(et_date) AS session,
        round((close_usd / prev_close - 1) * 100, 1) AS change_pct
    FROM changes
    WHERE prev_close > 0 AND isFinite(close_usd / prev_close)
),
(SELECT any(rank) FROM ranked WHERE session = '2008-09-29') AS tarp_rank_s,
(SELECT countIf(rank <= 10 AND session BETWEEN '2008-09-16' AND '2008-12-31') FROM ranked) AS post_lehman_s
SELECT
    session,
    rank,
    change_pct,
    tarp_rank_s AS tarp_vote_rank,
    post_lehman_s AS post_lehman_2008_top10
FROM ranked
WHERE rank <= 10 OR session = '2008-09-15'
ORDER BY rank