2,358 answered market questions

WITH sessions AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
           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-12-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-06-30')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY session_date
),
paired AS (
    SELECT session_date,
           toFloat64(session_open) AS open_px,
           toFloat64(session_close) AS close_px,
           any(toFloat64(session_close)) OVER (ORDER BY session_date ASC
                                               ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prior_close
    FROM sessions
)
SELECT formatDateTime(session_date, '%Y-%m-%d') AS date,
       formatDateTime(session_date, '%b %e, %Y') AS friday_label,
       round(100 * (open_px - prior_close) / prior_close, 2) AS open_gap_pct,
       round(100 * (close_px - open_px) / open_px, 2) AS open_to_close_pct
FROM paired
WHERE prior_close > 0
  AND session_date >= toDate('2025-01-01')
  AND toDayOfWeek(session_date) = 5
  AND toDayOfMonth(session_date) BETWEEN 15 AND 21
ORDER BY session_date