overnight_gaps

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.

SELECT
    symbol,
    round(avg(abs(gap_pct)), 2) AS avg_overnight_gap_pct,
    round(max(gap_pct), 1)      AS largest_up_gap_pct,
    round(abs(min(gap_pct)), 1) AS largest_down_gap_pct
FROM
(
    SELECT
        ticker                        AS symbol,
        100 * (open / prev_close - 1) AS gap_pct
    FROM
    (
        SELECT
            ticker,
            date,
            open,
            any(close) OVER (PARTITION BY ticker ORDER BY date
                             ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_close
        FROM
        (
            SELECT
                ticker,
                date,
                toFloat64(any(open))  AS open,
                toFloat64(any(close)) AS close
            FROM global_markets.stocks_daily_aggs
            WHERE ticker IN ('AAPL', 'KO', 'MSFT', 'NVDA', 'SPY')
              AND date >= today() - 400
            GROUP BY ticker, date
        )
    )
    WHERE prev_close > 0
)
GROUP BY symbol
ORDER BY largest_up_gap_pct DESC