2,309 answered market questions

WITH sessions AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
           toFloat64(argMin(open, window_start)) AS first_print,
           toFloat64(argMax(close, window_start)) AS last_print
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toDate(toTimeZone(window_start, 'America/New_York')) BETWEEN toDate('2023-12-01') AND 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 session_date
),
sequenced AS (
    SELECT session_date,
           first_print,
           last_print,
           any(last_print) OVER (ORDER BY session_date ASC
                                 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prior_close
    FROM sessions
)
SELECT session_date AS expiration_date,
       formatDateTime(session_date, '%b %e, %Y') AS expiry_label,
       round((first_print / prior_close - 1) * 100, 2) AS overnight_gap_pct,
       round((last_print / first_print - 1) * 100, 2) AS friday_session_pct
FROM sequenced
WHERE prior_close > 0
  AND session_date >= toDate('2024-01-01')
  AND toDayOfWeek(session_date) = 5
  AND toDayOfMonth(session_date) BETWEEN 15 AND 21
ORDER BY expiration_date