The ten deepest gap-down opens in SPY since 2006, and where those sessions traded

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 daily AS
(
    SELECT
        date,
        toFloat64(any(open))  AS open_px,
        toFloat64(any(high))  AS high_px,
        toFloat64(any(low))   AS low_px,
        toFloat64(any(close)) AS close_px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2006-01-01'
      AND date <  '2026-10-01'
    GROUP BY date
),
sessions AS
(
    SELECT
        date,
        open_px,
        high_px,
        low_px,
        lagInFrame(close_px) OVER (ORDER BY date ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prior_close
    FROM daily
)
SELECT
    formatDateTime(date, '%b %e, %Y')           AS label,
    round(100 * (1 - open_px / prior_close), 2) AS gap_below_prior_close_pct,
    round(100 * (1 - high_px / prior_close), 2) AS intraday_high_below_prior_close_pct,
    round(100 * (1 - low_px  / prior_close), 2) AS intraday_low_below_prior_close_pct
FROM sessions
WHERE prior_close > 0
ORDER BY open_px / prior_close ASC
LIMIT 10