Overnight gaps by ticker, H1 2026: average absolute gap and the single biggest gap

SELECT ticker,
       count() AS sessions,
       round(avg(abs(gap_pct)), 2) AS avg_abs_gap_pct,
       round(argMax(gap_pct, (abs(gap_pct), day)), 2) AS biggest_gap_pct,
       concat(monthName(argMax(day, (abs(gap_pct), day))), ' ', toString(toDayOfMonth(argMax(day, (abs(gap_pct), day)))), ', ', toString(toYear(argMax(day, (abs(gap_pct), day))))) AS biggest_gap_day
FROM (
    SELECT ticker, day, 100 * (rth_open - prior_close) / prior_close AS gap_pct
    FROM (
        SELECT ticker, day, rth_open,
               lagInFrame(rth_close) OVER (PARTITION BY ticker ORDER BY day) AS prior_close
        FROM (
            SELECT ticker, 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 ticker, 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 IN ('SPY', 'KO', 'AAPL', 'MSFT', 'NVDA', 'TSLA')
                  AND window_start >= '2025-12-29 04:00:00'
                  AND window_start < '2026-07-01 08:00:00'
            )
            GROUP BY ticker, day
        )
    )
    WHERE day >= '2026-01-01' AND prior_close > 0 AND isFinite(prior_close)
)
GROUP BY ticker
ORDER BY indexOf(['SPY', 'KO', 'AAPL', 'MSFT', 'NVDA', 'TSLA'], ticker)