2,170 answered market questions

WITH bars AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           argMin(open, window_start) AS session_open,
           argMax(close, window_start) AS session_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      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 d
),
linked AS (
    SELECT d,
           session_open,
           session_close,
           lagInFrame(session_close) OVER (ORDER BY d ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prior_close
    FROM bars
)
SELECT formatDateTime(toStartOfMonth(d), '%Y-%m') AS month,
       round(avg(abs(session_open / prior_close - 1)) * 100, 3) AS overnight_move_pct,
       round(avg(abs(session_close / session_open - 1)) * 100, 3) AS session_move_pct
FROM linked
WHERE prior_close > 0
GROUP BY month
ORDER BY month ASC