SPY on witching sessions vs. the same month's other sessions: intraday range and net move (% of the open)

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 day,
           toFloat64(max(high) - min(low)) / toFloat64(argMin(open, window_start)) * 100 AS range_pct,
           abs(toFloat64(argMax(close, window_start)) - toFloat64(argMin(open, window_start)))
               / toFloat64(argMin(open, window_start)) * 100 AS net_move_pct
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= '2024-09-01 04:00:00'
      AND window_start < '2026-07-01 04:00:00'
      AND toMonth(toTimeZone(window_start, 'America/New_York')) IN (3, 6, 9, 12)
      AND toHour(toTimeZone(window_start, 'America/New_York')) >= 9
      AND toHour(toTimeZone(window_start, 'America/New_York')) < 16
      AND NOT (toHour(toTimeZone(window_start, 'America/New_York')) = 9
               AND toMinute(toTimeZone(window_start, 'America/New_York')) < 30)
    GROUP BY day
),
witching AS (
    SELECT toStartOfMonth(day) AS m,
           maxIf(day, day <= addDays(toStartOfMonth(day),
                 ((5 - toDayOfWeek(toStartOfMonth(day)) + 7) % 7) + 14)) AS witching_day
    FROM daily
    GROUP BY m
)
SELECT toString(w.witching_day) AS witching_session,
       round(anyIf(d.range_pct, d.day = w.witching_day), 2) AS witching_range_pct,
       round(quantileDeterministicIf(0.5)(d.range_pct, cityHash64(toString(d.day)), d.day != w.witching_day), 2) AS other_days_median_range_pct,
       round(anyIf(d.net_move_pct, d.day = w.witching_day), 2) AS witching_net_move_pct,
       round(quantileDeterministicIf(0.5)(d.net_move_pct, cityHash64(toString(d.day)), d.day != w.witching_day), 2) AS other_days_median_net_move_pct
FROM daily AS d
INNER JOIN witching AS w ON toStartOfMonth(d.day) = w.m
GROUP BY w.witching_day
HAVING countIf(d.day != w.witching_day) > 0
ORDER BY w.witching_day