3,256 answered market questions

WITH trades AS
(
    SELECT
        toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS session_date,
        if(has(conditions, 8), '15:30',
           formatDateTime(
               toStartOfInterval(toTimeZone(sip_timestamp, 'America/New_York'), INTERVAL 30 MINUTE),
               '%H:%i'))                                     AS et_time,
        toFloat64(size)                                      AS shares
    FROM global_markets.stocks_trades
    WHERE ticker = 'SPY'
      AND sip_timestamp >= toDateTime('2026-06-01 04:00:00', 'UTC')
      AND sip_timestamp <  toDateTime('2026-07-01 04:00:00', 'UTC')
      AND NOT hasAny(conditions, [15, 16, 38])
      AND (has(conditions, 8)
           OR (toHour(toTimeZone(sip_timestamp, 'America/New_York')) * 60
               + toMinute(toTimeZone(sip_timestamp, 'America/New_York'))) BETWEEN 570 AND 959)
),
bucket_totals AS
(
    SELECT session_date, et_time, sum(shares) AS bucket_shares
    FROM trades
    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