2,173 answered market questions

WITH minute_bars AS
(
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
        formatDateTime(
            toStartOfInterval(toTimeZone(window_start, 'America/New_York'), INTERVAL 30 MINUTE),
            '%H:%i')                                         AS et_time,
        toFloat64(volume)                                    AS shares
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2026-06-01 00:00:00', 'UTC')
      AND window_start <  toDateTime('2026-07-01 00:00:00', 'UTC')
      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
),
bucket_totals AS
(
    SELECT session_date, et_time, sum(shares) AS bucket_shares
    FROM minute_bars
    GROUP BY session_date, et_time
),
session_totals AS
(
    SELECT session_date, sum(bucket_shares) AS session_shares
    FROM bucket_totals
    GROUP BY session_date
)
SELECT
    b.et_time                                               AS et_time,
    round(avg(b.bucket_shares / s.session_shares) * 100, 2) AS share_of_volume_pct
FROM bucket_totals AS b
INNER JOIN session_totals AS s ON s.session_date = b.session_date
GROUP BY b.et_time
ORDER BY b.et_time