3,256 answered market questions

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