Same-session gap fill rate by gap size, eight large caps, 2021 to 2026

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
    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
),
gapped AS
(
    SELECT
        d,
        session_open,
        session_high,
        session_low,
        lagInFrame(session_close) OVER (PARTITION BY ticker ORDER BY d ASC
            ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_close
    FROM sessions
),
measured AS
(
    SELECT
        abs(100 * (session_open / prior_close - 1)) AS gap_pct,
        toUInt8(if(session_open > prior_close,
                   session_low  <= prior_close,
                   session_high >= prior_close))    AS filled_same_session
    FROM gapped
    WHERE prior_close > 0
      AND d <= toDate('2026-02-28')
      AND abs(100 * (session_open / prior_close - 1)) >= 0.25
)
SELECT
    multiIf(gap_pct < 0.5, '0.25 to 0.5%',
            gap_pct < 1,   '0.5 to 1%',
            gap_pct < 2,   '1 to 2%',
            gap_pct < 4,   '2 to 4%',
                           '4% or more')         AS gap_bucket,
    count()                                      AS gap_days,
    round(100 * avg(filled_same_session), 1)     AS same_session_fill_pct
FROM measured
GROUP BY gap_bucket
ORDER BY min(gap_pct) ASC