Options tape: contracts, call %, 0DTE share vs Thursday, top contract, Friday expiry day

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) = '260710', 1, 0) AS is_0dte
            FROM global_markets.options_trades
            WHERE sip_timestamp >= '2026-07-10 00:00:00' AND sip_timestamp < '2026-07-11 00:00:00'
            GROUP BY ticker
            ORDER BY vol DESC
            LIMIT 2
        )
    ) AS top2,
    (
        SELECT round(toFloat64(argMax(close, window_start)), 2)
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY' AND window_start >= '2026-07-10 13:30:00' AND window_start < '2026-07-10 20:00:00'
    ) AS spy_regular_close,
    (
        SELECT round(toFloat64(sum(size)) / 1e6, 2)
        FROM global_markets.options_trades
        WHERE sip_timestamp >= '2026-07-09 00:00:00' AND sip_timestamp < '2026-07-10 00:00:00'
    ) AS jul9_contracts_m,
    (
        SELECT round(100.0 * sumIf(size, substring(ticker, length(ticker) - 14, 6) = '260709') / sum(size), 1)
        FROM global_markets.options_trades
        WHERE sip_timestamp >= '2026-07-09 00:00:00' AND sip_timestamp < '2026-07-10 00:00:00'
    ) AS jul9_pct_0dte
SELECT
    round(count() / 1e6, 2) AS option_prints_m,
    round(toFloat64(sum(size)) / 1e6, 2) AS contracts_m,
    jul9_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) = '260710') / sum(size), 1) AS pct_0dte,
    jul9_pct_0dte,
    spy_regular_close,
    top2.1[1] AS top1_und, top2.2[1] AS top1_strike, top2.3[1] AS top1_type, top2.4[1] AS top1_contracts, top2.5[1] AS top1_avg_px, top2.6[1] AS top1_is_0dte,
    round(toFloat64(top2.2[1]) - spy_regular_close, 2) AS top1_moneyness
FROM global_markets.options_trades
WHERE sip_timestamp >= '2026-07-10 00:00:00' AND sip_timestamp < '2026-07-11 00:00:00'