Share of SPY's regular-session volume by five-minute ET bucket

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 buckets AS
(
    SELECT
        formatDateTime(toDateTime(intDiv(et_minute_of_day, 5) * 5 * 60, 'UTC'), '%H:%i') AS et_time,
        toFloat64(sum(volume))                                                        AS bucket_volume
    FROM
    (
        SELECT
            toHour(toTimeZone(window_start, 'America/New_York')) * 60
                + toMinute(toTimeZone(window_start, 'America/New_York')) AS et_minute_of_day,
            volume
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= today() - 75
          AND window_start <  today() - 2
    )
    WHERE et_minute_of_day >= 570 AND et_minute_of_day <= 959
    GROUP BY et_time
    UNION ALL
    SELECT
        '16:00'           AS et_time,
        sum(print_shares) AS bucket_volume
    FROM
    (
        SELECT
            toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS session_date,
            toFloat64(maxIf(size, has(conditions, 8)))            AS print_shares
        FROM global_markets.stocks_trades
        WHERE ticker = 'SPY'
          AND sip_timestamp >= today() - 75
          AND sip_timestamp <  today() - 2
          AND toHour(sip_timestamp, 'America/New_York') IN (13, 16)
          AND toMinute(sip_timestamp, 'America/New_York') < 10
        GROUP BY session_date
    )
)
SELECT
    et_time,
    round(100 * bucket_volume / sum(bucket_volume) OVER (), 3) AS pct_of_session_volume
FROM buckets
ORDER BY et_time