DTE menus compared: expirations traded and 0DTE share by underlying, July 8, 2026

SELECT underlying,
       uniqExact(expiry) AS expiries_traded,
       min(dte) AS shortest_dte,
       max(dte) AS longest_dte,
       round(100.0 * sumIf(vol, dte = 0) / sum(vol), 1) AS pct_0dte,
       round(sum(vol) / 1e6, 2) AS contracts_mm
FROM (
    SELECT splitByChar(':', ticker)[2] AS raw,
           substring(raw, 1, length(raw) - 15) AS underlying,
           toFloat64(volume) AS vol,
           toDateOrNull(concat('20', substring(ticker, length(ticker) - 14, 6))) AS expiry,
           dateDiff('day', toDate(toTimeZone(window_start, 'America/New_York')), expiry) AS dte
    FROM global_markets.options_minute_aggs
    WHERE (ticker LIKE 'O:SPY2%' OR ticker LIKE 'O:QQQ2%' OR ticker LIKE 'O:IWM2%'
           OR ticker LIKE 'O:AAPL2%' OR ticker LIKE 'O:TSLA2%' OR ticker LIKE 'O:KO2%')
      AND window_start >= '2026-07-08 04:00:00'
      AND window_start < '2026-07-09 04:00:00'
)
WHERE dte >= 0
GROUP BY underlying
ORDER BY contracts_mm DESC