3,293 answered market questions

WITH day_totals AS
(
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
        count()                                              AS regular_bars,
        sum(volume)                                          AS regular_volume
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= '2025-11-20 00:00:00'
      AND window_start <  '2026-01-03 06:00:00'
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) < 960
    GROUP BY et_date
)
SELECT
    if(regular_bars < 300, 'Early close 1 p.m.', 'Full session 4 p.m.') AS category,
    count()                                                            AS sessions,
    round(avg(regular_bars))                                           AS avg_minute_bars,
    round(avg(regular_volume) / 1e6, 1)                                AS avg_volume_millions
FROM day_totals
GROUP BY category
ORDER BY avg_minute_bars