One row for the whole options day: volume, same-day expiry, put/call skew, and the busiest contract

WITH
    (
        SELECT (any(underlying_symbol), any(toFloat64(strike_price)), any(option_type),
                sum(size), round(avg(toFloat64(price)), 3),
                any(if(substring(ticker, length(ticker) - 14, 6) = '260701', 1, 0)))
        FROM global_markets.options_trades
        WHERE sip_timestamp >= '2026-07-01 00:00:00' AND sip_timestamp < '2026-07-02 00:00:00'
        GROUP BY ticker
        ORDER BY sum(size) DESC, ticker ASC
        LIMIT 1
    ) AS top_contract,
    (
        SELECT round(toFloat64(argMax(close, window_start)), 2)
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY' AND window_start >= '2026-07-01 13:30:00' AND window_start < '2026-07-01 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(sumIf(toFloat64(size), option_type = 'P') / sumIf(toFloat64(size), option_type = 'C'), 2) AS put_call_ratio,
    round(100.0 * sumIf(size, substring(ticker, length(ticker) - 14, 6) = '260701') / sum(size), 1) AS same_day_expiry_pct,
    round(toFloat64(sumIf(size, substring(ticker, length(ticker) - 14, 6) = '260702')) / 1e6, 2) AS thu_jul2_expiry_contracts_m,
    countIf(substring(ticker, length(ticker) - 14, 6) = '260703') AS fri_jul3_expiry_prints,
    round(toFloat64(sumIf(size, underlying_symbol = 'SPY' AND option_type = 'P')) / 1e6, 2) AS spy_put_contracts_m,
    round(100.0 * sumIf(size, underlying_symbol = 'SPY' AND option_type = 'P' AND toFloat64(strike_price) < spy_regular_close * 0.98)
        / sumIf(size, underlying_symbol = 'SPY' AND option_type = 'P'), 1) AS spy_puts_2pct_below_pct,
    round(100.0 * sumIf(size, underlying_symbol = 'SPY' AND option_type = 'C' AND toFloat64(strike_price) > spy_regular_close * 1.02)
        / sumIf(size, underlying_symbol = 'SPY' AND option_type = 'C'), 1) AS spy_calls_2pct_above_pct,
    round(sumIf(toFloat64(size), underlying_symbol = 'MU' AND option_type = 'P')
        / sumIf(toFloat64(size), underlying_symbol = 'MU' AND option_type = 'C'), 2) AS mu_put_call_ratio,
    round(sumIf(toFloat64(size), underlying_symbol = 'META' AND option_type = 'P')
        / sumIf(toFloat64(size), underlying_symbol = 'META' AND option_type = 'C'), 2) AS meta_put_call_ratio,
    top_contract.1 AS top_contract_underlying,
    top_contract.2 AS top_contract_strike,
    top_contract.3 AS top_contract_type,
    top_contract.4 AS top_contract_volume,
    reverse(arrayStringConcat(extractAll(reverse(toString(assumeNotNull(top_contract.4))), '\\d{1,3}'), ',')) AS top_contract_volume_fmt,
    round(top_contract.5, 3) AS top_contract_avg_price,
    top_contract.6 AS top_contract_is_same_day,
    round(top_contract.2 - spy_regular_close, 2) AS top_strike_minus_spy_close,
    spy_regular_close AS spy_close
FROM global_markets.options_trades
WHERE sip_timestamp >= '2026-07-01 00:00:00' AND sip_timestamp < '2026-07-02 00:00:00'