Average distance from one session's close to the next session's open, monthly

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 sessions AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS session,
           argMin(open, window_start) AS session_open,
           argMax(close, window_start) AS session_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'NVDA')
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2024-08-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-07-31')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, session
),
gaps AS (
    SELECT ticker,
           session,
           toFloat64(session_open) AS open_px,
           toFloat64(lagInFrame(session_close) OVER (PARTITION BY ticker ORDER BY session)) AS prev_close
    FROM sessions
)
SELECT formatDateTime(toStartOfMonth(session), '%Y-%m') AS month,
       formatDateTimeInJodaSyntax(toStartOfMonth(session), 'MMMM yyyy') AS month_label,
       round(avgIf(abs(open_px / prev_close - 1) * 10000, ticker = 'SPY'), 1) AS spy_gap_bps,
       round(avgIf(abs(open_px / prev_close - 1) * 10000, ticker = 'NVDA'), 1) AS nvda_gap_bps
FROM gaps
WHERE prev_close > 0
GROUP BY month, month_label
HAVING countIf(ticker = 'SPY') > 0 AND countIf(ticker = 'NVDA') > 0
ORDER BY month