gap_distribution

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
    last_prints AS
    (
        SELECT
            ticker,
            toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
            argMax(close, window_start)                          AS last_regular_print
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'SPY', 'KO', 'JNJ')
          AND window_start >= '2026-07-01 00:00:00'
          AND window_start <  '2026-10-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
        GROUP BY ticker, session_date
    ),
    daily_bars AS
    (
        SELECT
            ticker,
            date       AS session_date,
            any(close) AS daily_bar_close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'SPY', 'KO', 'JNJ')
          AND date >= '2026-07-01'
          AND date <  '2026-10-01'
        GROUP BY ticker, session_date
    ),
    gaps AS
    (
        SELECT
            l.session_date AS session_date,
            abs(toFloat64(d.daily_bar_close) / toFloat64(l.last_regular_print) - 1) * 10000 AS gap_bps
        FROM last_prints AS l
        INNER JOIN daily_bars AS d
            ON l.ticker = d.ticker AND l.session_date = d.session_date
        WHERE toFloat64(l.last_regular_print) > 0
    ),
    totals AS
    (
        SELECT count() AS all_rows
        FROM gaps
    )
SELECT
    multiIf(g.gap_bps < 0.5, '0.0 to 0.5 bps',
            g.gap_bps < 2,   '0.5 to 2 bps',
            g.gap_bps < 5,   '2 to 5 bps',
            g.gap_bps < 10,  '5 to 10 bps',
            g.gap_bps < 25,  '10 to 25 bps',
                             '25 bps and up') AS gap_bucket,
    count()                                   AS ticker_sessions,
    round(100 * count() / any(t.all_rows), 1) AS share_of_sessions_pct
FROM gaps AS g
CROSS JOIN totals AS t
GROUP BY gap_bucket
ORDER BY min(g.gap_bps)