Options tape: prints, contracts, call %, 0DTE share vs Wednesday, top contracts

WITH
    (
        SELECT (groupArray(und), groupArray(strike), groupArray(typ), groupArray(vol), groupArray(avg_px), groupArray(is_0dte))
        FROM (
            SELECT any(underlying_symbol) AS und, any(toFloat64(strike_price)) AS strike, any(option_type) AS typ,
                   sum(size) AS vol, round(avg(toFloat64(price)), 3) AS avg_px,
                   if(substring(ticker, length(ticker) - 14, 6) = '260709', 1, 0) AS is_0dte
            FROM global_markets.options_trades
            WHERE sip_timestamp >= '2026-07-09 00:00:00' AND sip_timestamp < '2026-07-10 00:00:00'
            GROUP BY ticker
            ORDER BY vol DESC
            LIMIT 3
        )
    ) AS top3,
    (
        SELECT round(toFloat64(argMax(close, window_start)), 2)
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY' AND window_start >= '2026-07-09 13:30:00' AND window_start < '2026-07-09 20:00:00'
    ) AS spy_regular_close,
    (
        SELECT round(toFloat64(sum(size)) / 1e6, 2)
        FROM global_markets.options_trades
        WHERE sip_timestamp >= '2026-07-08 00:00:00' AND sip_timestamp < '2026-07-09 00:00:00'
    ) AS jul8_contracts_m,
    (
        SELECT round(100.0 * sumIf(size, substring(ticker, length(ticker) - 14, 6) = '260708') / sum(size), 1)
        FROM global_markets.options_trades
        WHERE sip_timestamp >= '2026-07-08 00:00:00' AND sip_timestamp < '2026-07-09 00:00:00'
    ) AS jul8_pct_0dte
SELECT
    round(count() / 1e6, 2) AS option_prints_m,
    round(toFloat64(sum(size)) / 1e6, 2) AS contracts_m,
    jul8_contracts_m,
    round(100.0 * sumIf(size, option_type = 'C') / sum(size), 1) AS call_pct_of_volume,
    round(100.0 * sumIf(size, substring(ticker, length(ticker) - 14, 6) = '260709') / sum(size), 1) AS pct_0dte,
    jul8_pct_0dte,
    spy_regular_close,
    top3.1[1] AS top1_und, top3.2[1] AS top1_strike, top3.3[1] AS top1_type,
    round(toFloat64(top3.4[1]) / 1e3, 1) AS top1_contracts_k, top3.5[1] AS top1_avg_px, top3.6[1] AS top1_is_0dte,
    top3.1[2] AS top2_und, top3.2[2] AS top2_strike, top3.3[2] AS top2_type, top3.6[2] AS top2_is_0dte,
    top3.1[3] AS top3_und, top3.2[3] AS top3_strike, top3.3[3] AS top3_type, top3.6[3] AS top3_is_0dte,
    round(toFloat64(top3.2[1]) - spy_regular_close, 2) AS top1_moneyness
FROM global_markets.options_trades
WHERE sip_timestamp >= '2026-07-09 00:00:00' AND sip_timestamp < '2026-07-10 00:00:00'