The ten largest opening steps in SPY, 2016 to 2026

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,
            any(toFloat64(open))  AS session_open,
            any(toFloat64(close)) AS session_close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= '2016-01-01'
          AND date <  '2026-10-01'
        GROUP BY date
    ),
    gapped AS
    (
        SELECT
            date,
            session_open,
            lagInFrame(session_close) OVER (ORDER BY date ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prior_close
        FROM daily
    )
SELECT
    formatDateTime(date, '%b %e, %Y')                  AS session_label,
    formatDateTime(date, '%W')                         AS weekday,
    if(session_open < prior_close,
       'below the prior close',
       'above the prior close')                        AS direction,
    round(abs(session_open / prior_close - 1) * 100, 2) AS abs_gap_pct
FROM gapped
WHERE prior_close > 0
ORDER BY abs_gap_pct DESC
LIMIT 10