Gap fill rate by how heavy the gap day's volume was

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 sessions AS
(
    SELECT
        ticker,
        toDate(toTimeZone(window_start, 'America/New_York')) AS d,
        argMin(toFloat64(open), window_start)                AS session_open,
        argMax(toFloat64(close), window_start)               AS session_close,
        toFloat64(max(high))                                 AS session_high,
        toFloat64(min(low))                                  AS session_low,
        sum(toFloat64(volume))                               AS session_volume
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'AMZN', 'JPM', 'KO', 'WMT', 'XOM')
      AND window_start >= '2021-01-01'
      AND window_start <  '2026-07-01'
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) < 960
    GROUP BY ticker, d
),
paths AS
(
    SELECT
        d,
        session_open,
        session_high,
        session_low,
        session_volume,
        lagInFrame(session_close) OVER (PARTITION BY ticker ORDER BY d ASC
            ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)                                                         AS prior_close,
        avg(session_volume) OVER (PARTITION BY ticker ORDER BY d ASC ROWS BETWEEN 21 PRECEDING AND 2 PRECEDING) AS normal_volume,
        min(session_low)  OVER (PARTITION BY ticker ORDER BY d ASC ROWS BETWEEN CURRENT ROW AND 19 FOLLOWING) AS low_20,
        max(session_high) OVER (PARTITION BY ticker ORDER BY d ASC ROWS BETWEEN CURRENT ROW AND 19 FOLLOWING) AS high_20
    FROM sessions
),
measured AS
(
    SELECT
        session_volume / normal_volume AS volume_ratio,
        toUInt8(if(session_open > prior_close, session_low <= prior_close, session_high >= prior_close)) AS filled_same_session,
        toUInt8(if(session_open > prior_close, low_20 <= prior_close, high_20 >= prior_close))           AS filled_20_sessions
    FROM paths
    WHERE prior_close > 0
      AND normal_volume > 0
      AND d >= toDate('2021-03-01')
      AND d <= toDate('2026-02-28')
      AND abs(100 * (session_open / prior_close - 1)) >= 1
)
SELECT
    multiIf(volume_ratio < 1.5, 'Under 1.5x normal',
            volume_ratio < 3,   '1.5x to 3x normal',
                                '3x or more')        AS volume_regime,
    count()                                          AS gap_days,
    round(100 * avg(filled_same_session), 1)         AS same_session_fill_pct,
    round(100 * avg(filled_20_sessions), 1)          AS within_20_sessions_fill_pct
FROM measured
WHERE isFinite(volume_ratio)
GROUP BY volume_regime
ORDER BY min(volume_ratio) ASC