SPY overnight gaps by weekday: every session, January 2024 through June 2026

SELECT ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday'][dow] AS weekday,
       count() AS sessions,
       round(avg(abs(gap_pct)), 2) AS avg_abs_gap_pct,
       round(quantileDeterministic(0.9)(abs(gap_pct), toUInt64(toYYYYMMDD(day))), 2) AS p90_abs_gap_pct
FROM (
    SELECT day, toDayOfWeek(day) AS dow,
           100 * (rth_open - prior_close) / prior_close AS gap_pct
    FROM (
        SELECT day, rth_open, 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 >= '2023-12-28 04:00:00'
                  AND window_start < '2026-07-01 08:00:00'
            )
            GROUP BY day
        )
    )
    WHERE day >= '2024-01-01' AND prior_close > 0 AND isFinite(prior_close)
)
WHERE dow <= 5
GROUP BY dow
ORDER BY dow