SPY, H1 2026: average absolute overnight gap vs average absolute intraday move

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.

SELECT count() AS sessions,
       round(avg(abs(100 * (rth_open - prior_close) / prior_close)), 2) AS avg_abs_overnight_gap_pct,
       round(avg(abs(100 * (rth_close - rth_open) / rth_open)), 2) AS avg_abs_intraday_move_pct,
       countIf(abs(100 * (rth_open - prior_close) / prior_close) >= 0.5) AS gaps_of_half_pct_or_more
FROM (SELECT day, rth_open, rth_close, lagInFrame(rth_close) OVER (ORDER BY day) AS prior_close
    FROM (
        SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS day,
               argMinIf(toFloat64(open), window_start, rth) AS rth_open,
               argMaxIf(toFloat64(close), window_start, rth) AS rth_close
        FROM (
            SELECT window_start, open, close,
                   toTimeZone(window_start, 'America/New_York') >= toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 09:30:00'), 'America/New_York')
                   AND toTimeZone(window_start, 'America/New_York') < toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 16:00:00'), 'America/New_York') AS rth
            FROM global_markets.delayed_stocks_minute_aggs
            WHERE ticker = 'SPY'
              AND window_start >= '2025-12-29 04:00:00'
              AND window_start < '2026-07-01 08:00:00'
        )
        GROUP BY day
    ))
WHERE day >= '2026-01-01' AND prior_close > 0 AND isFinite(prior_close)