SPY across recent weekends and holiday weekends: Friday's close vs. the reopening print

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 daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           round(argMin(toFloat64(open), window_start), 2) AS open_px,
           round(argMax(toFloat64(close), window_start), 2) AS close_px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= today() - 75
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY d
),
paired AS (
    SELECT d,
           open_px,
           lagInFrame(d) OVER (ORDER BY d) AS prev_d,
           lagInFrame(close_px) OVER (ORDER BY d) AS prev_close
    FROM daily
)
SELECT concat(formatDateTime(prev_d, '%a '), toString(prev_d)) AS last_session_before,
       concat(formatDateTime(d, '%a '), toString(d)) AS reopening_session,
       dateDiff('day', prev_d, d) AS days_dark,
       toString(prev_close) AS close_before_break,
       toString(open_px) AS reopening_print,
       round((open_px - prev_close) / prev_close * 100, 2) AS gap_pct
FROM paired
WHERE prev_close > 0 AND dateDiff('day', prev_d, d) >= 3
ORDER BY d