The 15 minute gap across six household tickers (June 2026)

WITH regular_bars AS
(
    SELECT
        ticker,
        toTimeZone(window_start, 'America/New_York')                      AS et,
        toTimeZone(window_start, 'America/New_York') + INTERVAL 15 MINUTE AS et_later,
        toFloat64(close)                                                  AS px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'KO', 'AAPL', 'MSFT', 'NVDA', 'AMD')
      AND close > 0
      AND window_start >= '2026-06-01 00:00:00'
      AND window_start <  '2026-07-01 00:00:00'
      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
)
SELECT
    a.ticker                                                                                           AS ticker,
    round(quantileDeterministic(0.5)(abs(b.px / a.px - 1) * 100, toUInt64(toUnixTimestamp(a.et))), 3)   AS median_gap_pct,
    round(quantileDeterministic(0.99)(abs(b.px / a.px - 1) * 100, toUInt64(toUnixTimestamp(a.et))), 3)  AS p99_gap_pct
FROM regular_bars AS a
INNER JOIN regular_bars AS b ON b.ticker = a.ticker AND b.et = a.et_later
GROUP BY ticker
ORDER BY median_gap_pct DESC