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,
           toFloat64(sumIf(volume, (toHour(toTimeZone(window_start, 'America/New_York')) * 60
                + toMinute(toTimeZone(window_start, 'America/New_York'))) = 570)) AS open_minute_shares
    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,
           open_minute_shares,
           any(last_print) OVER (ORDER BY session_date ASC
                                 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prior_close
    FROM sessions
),
labelled AS (
    SELECT session_date,
           multiIf(toDayOfWeek(session_date) = 5
                       AND toDayOfMonth(session_date) BETWEEN 15 AND 21
                       AND toMonth(session_date) IN (3, 6, 9, 12), 'triple witching Friday',
                   toDayOfWeek(session_date) = 5
                       AND toDayOfMonth(session_date) BETWEEN 15 AND 21, 'other monthly expiration',
                   'ordinary session') AS bucket,
           abs(first_print / prior_close - 1) * 100 AS gap_abs_raw,
           abs(greatest(last_print - round(prior_close, 0), 0)
               - greatest(first_print - round(prior_close, 0), 0)) AS spread_raw,
           open_minute_shares / 1000000 AS open_minute_millions
    FROM sequenced
    WHERE prior_close > 0
      AND session_date >= toDate('2024-01-01')
)
SELECT bucket,
       count() AS group_size,
       round(quantileDeterministic(0.5)(gap_abs_raw, cityHash64(session_date)), 2) AS median_gap_abs_pct,
       round(quantileDeterministic(0.5)(spread_raw, cityHash64(session_date)), 2) AS median_settlement_spread_usd,
       round(quantileDeterministic(0.5)(open_minute_millions, cityHash64(session_date)), 2) AS median_open_minute_volume_m
FROM labelled
GROUP BY bucket
ORDER BY group_size ASC