gap_buckets

Name: gap_buckets
Creator: Strasmore
Published: 2026-09-26
License: https://www.strasmore.com/terms/

Answered against 22 years of US equities and 12 years of US options data and published with the query that produced it. This result is stored as of 2026-09-26, from msft-premarket-and-after-hours-prices.

as of 2026-09-26 ranking 5×3read in context →

gap_buckets — 5 rows by 3 columns, computed 2026-09-26 from US exchange, SIP and OPRA data.
gap_bucket	sessions	share_pct
0.00-0.25%	869	32.4
0.25-0.50%	649	24.2
0.50-1.00%	658	24.6
1.00-2.00%	365	13.6
2.00%+	138	5.2

Rows × columns: 5 × 3
Computed: 2026-09-26
Completeness: No missing values
Source: US exchange, SIP and OPRA market data
Licence: Strasmore terms · free, no signup
Formats: JSON · CSV · the SQL below

What each column holds

Column definitions for gap_buckets, derived from the stored result.
Column	Type	Range	Notes
`gap_bucket`	text	5 distinct values (0.00-0.25%, 0.25-0.50%, 0.50-1.00%…)
`sessions`	number	138 to 869
`share_pct`	number	5.2 to 32.4	percent

Computed from Strasmore's warehouse of US exchange, SIP and OPRA market data. Equity prices are delayed; options greeks and implied volatility are end-of-day. This result is stored, not recomputed on load — it is exactly the numbers that were returned on 2026-09-26, and the query below is what returned them.

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 bars AS
(
    SELECT
        date,
        any(open)  AS open_px,
        any(close) AS close_px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'MSFT'
      AND date >= '2016-01-01'
      AND date <  '2026-09-01'
    GROUP BY date
),
gaps AS
(
    SELECT
        date,
        round((toFloat64(open_px) / prev_close - 1) * 100, 3) AS gap_pct
    FROM
    (
        SELECT
            date,
            open_px,
            lagInFrame(toFloat64(close_px)) OVER (ORDER BY date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
        FROM bars
    )
    WHERE prev_close > 0
)
SELECT
    multiIf(abs(gap_pct) < 0.25, '0.00-0.25%',
            abs(gap_pct) < 0.50, '0.25-0.50%',
            abs(gap_pct) < 1.00, '0.50-1.00%',
            abs(gap_pct) < 2.00, '1.00-2.00%',
                                 '2.00%+')          AS gap_bucket,
    count()                                         AS sessions,
    round(100.0 * count() / sum(count()) OVER (), 1) AS share_pct
FROM gaps
GROUP BY gap_bucket
ORDER BY min(abs(gap_pct)) ASC

SQL to run

WITH bars AS
(
    SELECT
        date,
        any(open)  AS open_px,
        any(close) AS close_px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'MSFT'
      AND date >= '2016-01-01'
      AND date <  '2026-09-01'
    GROUP BY date
),
gaps AS
(
    SELECT
        date,
        round((toFloat64(open_px) / prev_close - 1) * 100, 3) AS gap_pct
    FROM
    (
        SELECT
            date,
            open_px,
            lagInFrame(toFloat64(close_px)) OVER (ORDER BY date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
        FROM bars
    )
    WHERE prev_close > 0
)
SELECT
    multiIf(abs(gap_pct) < 0.25, '0.00-0.25%',
            abs(gap_pct) < 0.50, '0.25-0.50%',
            abs(gap_pct) < 1.00, '0.50-1.00%',
            abs(gap_pct) < 2.00, '1.00-2.00%',
                                 '2.00%+')          AS gap_bucket,
    count()                                         AS sessions,
    round(100.0 * count() / sum(count()) OVER (), 1) AS share_pct
FROM gaps
GROUP BY gap_bucket
ORDER BY min(abs(gap_pct)) ASC

⌘/Ctrl + Enter

Work with this data in your AI assistant

Opens ready to query, with this page's data. Free, no account.

Claude ChatGPT Gemini Muse Your own agent