bias — 1 answered market questions

Distance from one close to the next open, liquid names, 2025 (basis points)

most recentas of 2026-09-16 ranking 5×3read in context →

Distance from one close to the next open, liquid names, 2025 (basis points) — 5 rows by 3 columns, computed 2026-09-16 from US exchange, SIP and OPRA data.
ticker	avg_abs_gap_bps	median_abs_gap_bps
AAPL	73.3	36.4
MSFT	66.3	39.8
TLT	47.1	37.5
SPY	46.7	28.5
KO	38.1	23.5

the exact SQL behind every number

WITH
    px AS
    (
        SELECT
            ticker,
            date,
            toFloat64(open)  AS open_px,
            toFloat64(close) AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('SPY', 'AAPL', 'MSFT', 'KO', 'TLT')
          AND date >= '2025-01-01'
          AND date <= '2025-12-31'
    ),
    gaps AS
    (
        SELECT
            ticker,
            date,
            close_px,
            leadInFrame(open_px) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS next_open
        FROM px
    )
SELECT
    ticker,
    round(avg(abs(next_open / close_px - 1)) * 10000, 1)     AS avg_abs_gap_bps,
    round(median(abs(next_open / close_px - 1)) * 10000, 1)  AS median_abs_gap_bps
FROM gaps
WHERE next_open > 0
GROUP BY ticker
ORDER BY avg_abs_gap_bps DESC, ticker ASC