Between-session gaps on four equity index ETFs, 2015 to date

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH
    daily AS
    (
        SELECT
            ticker,
            date,
            any(open)  AS session_open,
            any(close) AS session_close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('SPY', 'QQQ', 'DIA', 'IWM')
          AND date >= '2015-01-01'
          AND date <  '2026-10-01'
        GROUP BY ticker, date
    ),
    gaps AS
    (
        SELECT
            ticker,
            date,
            abs(toFloat64(session_open)
                / any(toFloat64(session_close)) OVER (PARTITION BY ticker ORDER BY date ASC
                                                      ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
                - 1) AS gap
        FROM daily
    )
SELECT
    ticker                                                                       AS symbol,
    count()                                                                      AS sample_size,
    round(100 * quantileDeterministic(0.5)(gap, toUInt64(toYYYYMMDD(date))), 3)  AS median_gap_pct,
    round(100 * quantileDeterministic(0.99)(gap, toUInt64(toYYYYMMDD(date))), 2) AS p99_gap_pct,
    round(100 * max(gap), 2)                                                     AS largest_gap_pct
FROM gaps
WHERE isFinite(gap)
  AND gap > 0
GROUP BY ticker
ORDER BY p99_gap_pct DESC