3,256 answered market questions

WITH tape AS
(
    SELECT
        toHour(toTimeZone(window_start, 'America/New_York')) * 60
      + toMinute(toTimeZone(window_start, 'America/New_York'))              AS minute_of_day,
        countDistinct(toDate(toTimeZone(window_start, 'America/New_York'))) AS days_traded,
        sum(volume)                                                         AS contracts
    FROM global_markets.options_minute_aggs
    WHERE startsWith(ticker, 'O:SPY')
      AND length(ticker) = 20
      AND window_start >= today() - 12
      AND window_start <  today() - 2
    GROUP BY minute_of_day
)
SELECT
    formatDateTime(toDateTime('2026-01-01 00:00:00') + grid.minute_of_day * 60, '%H:%i')         AS et_time,
    toUInt64(round(ifNull(t.contracts, 0) / greatest((SELECT max(days_traded) FROM tape), 1)))   AS contract_volume,
    round(100 * ifNull(t.days_traded, 0) / greatest((SELECT max(days_traded) FROM tape), 1), 1)  AS active_pct
FROM
(
    SELECT 555 + arrayJoin(range(46)) AS minute_of_day
) AS grid
LEFT JOIN tape AS t ON t.minute_of_day = grid.minute_of_day
ORDER BY grid.minute_of_day