3,214 answered market questions

WITH
    tape AS
    (
        SELECT
            intDiv(
                toHour(toTimeZone(window_start, 'America/New_York')) * 60
              + toMinute(toTimeZone(window_start, 'America/New_York')), 30) * 30 AS bucket_minute,
            sum(volume)                                                          AS contracts
        FROM global_markets.options_minute_aggs
        WHERE window_start >= today() - 12
          AND window_start <  today() - 2
        GROUP BY bucket_minute
    ),
    shares AS
    (
        SELECT
            bucket_minute,
            contracts,
            round(100 * contracts / sum(contracts) OVER (), 2) AS share_pct
        FROM tape
    )
SELECT
    formatDateTime(toDateTime('2026-01-01 00:00:00') + grid.bucket_minute * 60, '%H:%i') AS et_time,
    round(ifNull(s.contracts, 0) / 1000000, 2)                                           AS contracts_millions,
    ifNull(s.share_pct, 0)                                                               AS share_pct
FROM
(
    SELECT 420 + 30 * arrayJoin(range(21)) AS bucket_minute
) AS grid
LEFT JOIN shares AS s ON s.bucket_minute = grid.bucket_minute
ORDER BY grid.bucket_minute