One row for the whole options day: volume, call share, 0DTE, and the two contracts that bracketed SPY's close

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) = '260707', 1, 0) AS is_0dte
            FROM global_markets.options_trades
            WHERE sip_timestamp >= '2026-07-07 00:00:00' AND sip_timestamp < '2026-07-08 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-07 13:30:00' AND window_start < '2026-07-07 20:00:00'
    ) AS spy_regular_close
SELECT
    round(count() / 1e6, 2) AS option_prints_m,
    round(toFloat64(sum(size)) / 1e6, 2) AS 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) = '260707') / sum(size), 1) AS same_day_expiry_pct,
    round(toFloat64(sumIf(size, substring(ticker, length(ticker) - 14, 6) = '260707')) / 1e6, 2) AS same_day_contracts_m,
    round(toFloat64(sumIf(size, underlying_symbol = 'SPY')) / 1e6, 2) AS spy_contracts_m,
    round(toFloat64(sumIf(size, underlying_symbol = 'QQQ')) / 1e6, 2) AS qqq_contracts_m,
    round(toFloat64(sumIf(size, underlying_symbol = 'INTC')) / 1e6, 2) AS intc_contracts_m,
    top2.1[1] AS top_contract_underlying,
    top2.2[1] AS top_contract_strike,
    top2.3[1] AS top_contract_type,
    if(top2.3[1] = 'C', 1, 0) AS top_contract_is_call,
    top2.4[1] AS top_contract_volume,
    top2.5[1] AS top_contract_avg_price,
    round(top2.2[1] - spy_regular_close, 2) AS top_strike_minus_spy_close,
    top2.1[2] AS second_contract_underlying,
    top2.2[2] AS second_contract_strike,
    top2.3[2] AS second_contract_type,
    if(top2.3[2] = 'P', 1, 0) AS second_contract_is_put,
    top2.4[2] AS second_contract_volume,
    top2.5[2] AS second_contract_avg_price,
    round(spy_regular_close - top2.2[2], 2) AS spy_close_minus_second_strike,
    top2.6[1] + top2.6[2] AS both_top_contracts_same_day,
    spy_regular_close AS spy_close
FROM global_markets.options_trades
WHERE sip_timestamp >= '2026-07-07 00:00:00' AND sip_timestamp < '2026-07-08 00:00:00'