Share of 1%+ gaps filled, by how long you wait

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
),
paths 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,
        min(session_low)  OVER (PARTITION BY ticker ORDER BY d ASC ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING)   AS low_5,
        max(session_high) OVER (PARTITION BY ticker ORDER BY d ASC ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING)   AS high_5,
        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,
        min(session_low)  OVER (PARTITION BY ticker ORDER BY d ASC ROWS BETWEEN CURRENT ROW AND 59 FOLLOWING)  AS low_60,
        max(session_high) OVER (PARTITION BY ticker ORDER BY d ASC ROWS BETWEEN CURRENT ROW AND 59 FOLLOWING)  AS high_60
    FROM sessions
),
gaps AS
(
    SELECT
        session_open > prior_close AS gap_up,
        prior_close,
        session_low,
        session_high,
        low_5,
        high_5,
        low_20,
        high_20,
        low_60,
        high_60
    FROM paths
    WHERE prior_close > 0
      AND d <= toDate('2026-02-28')
      AND abs(100 * (session_open / prior_close - 1)) >= 1
),
flags AS
(
    SELECT arrayJoin([
        (1, '1 session',   toUInt8(if(gap_up, session_low <= prior_close, session_high >= prior_close))),
        (2, '5 sessions',  toUInt8(if(gap_up, low_5  <= prior_close, high_5  >= prior_close))),
        (3, '20 sessions', toUInt8(if(gap_up, low_20 <= prior_close, high_20 >= prior_close))),
        (4, '60 sessions', toUInt8(if(gap_up, low_60 <= prior_close, high_60 >= prior_close)))
    ]) AS f
    FROM gaps
)
SELECT
    tupleElement(f, 2)                        AS horizon,
    count()                                   AS gaps_measured,
    round(100 * avg(tupleElement(f, 3)), 1)   AS filled_pct
FROM flags
GROUP BY horizon
ORDER BY min(tupleElement(f, 1)) ASC