SPY volume by half hour, premarket through after-hours (same session, ET clock)

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
(
    SELECT max(d)
    FROM
    (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS d,
            countIf((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) AS rth_bars
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= today() - 21
          AND window_start < today() - 2
        GROUP BY d
        HAVING rth_bars = 390
    )
) AS session_day
SELECT
    formatDateTime(toStartOfInterval(if(has(conditions, 8),
                                         toTimeZone(sip_timestamp, 'America/New_York') - INTERVAL 10 MINUTE,
                                         toTimeZone(sip_timestamp, 'America/New_York')),
                                      INTERVAL 30 MINUTE), '%H:%i') AS et_half_hour,
    round(sum(toFloat64(size)) / 1e6, 2) AS shares_m
FROM global_markets.stocks_trades
WHERE ticker = 'SPY'
  AND sip_timestamp >= toDateTime(session_day, 'America/New_York')
  AND sip_timestamp <  toDateTime(session_day + 1, 'America/New_York')
  AND NOT hasAny(conditions, [15, 16, 38])
GROUP BY et_half_hour
ORDER BY et_half_hour