NVDA options, full week: totals, put-call vs trailing baseline, strikes and expiries decoded

WITH
    (
        SELECT ticker
        FROM global_markets.options_trades
        WHERE startsWith(ticker, 'O:NVDA') AND length(ticker) = 21
          AND sip_timestamp >= toDateTime64('2026-07-06 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-11 00:00:00', 9)
        GROUP BY ticker ORDER BY sum(size) DESC, ticker ASC LIMIT 1
    ) AS busiest_t,
    (
        SELECT round(toFloat64(sumIf(size, substring(ticker, 13, 1) = 'P')) / toFloat64(sumIf(size, substring(ticker, 13, 1) = 'C')), 2)
        FROM global_markets.options_trades
        WHERE startsWith(ticker, 'O:NVDA') AND length(ticker) = 21
          AND sip_timestamp >= toDateTime64('2026-06-08 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-03 00:00:00', 9)
    ) AS trailing_pc,
    (
        SELECT argMax(toFloat64(close), window_start)
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'NVDA' AND window_start >= toDateTime('2026-07-10 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
          AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
    ) AS friday_close,
    (
        SELECT argMax(toFloat64(close), window_start)
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'NVDA' AND window_start >= toDateTime('2026-07-02 00:00:00') AND window_start < toDateTime('2026-07-03 00:00:00')
          AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
    ) AS start_close,
    (
        SELECT concat(substring(e, 3, 2), '-', substring(e, 5, 2))
        FROM (
            SELECT substring(ticker, 7, 6) AS e, sum(size) AS v
            FROM global_markets.options_trades
            WHERE startsWith(ticker, 'O:NVDA') AND length(ticker) = 21
              AND substring(ticker, 7, 6) > '260710'
              AND sip_timestamp >= toDateTime64('2026-07-06 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-11 00:00:00', 9)
            GROUP BY e ORDER BY v DESC, e ASC LIMIT 1
        )
    ) AS back_expiry
SELECT
    round(sum(size) / 1e6, 2) AS contracts_traded_m,
    round(count() / 1e6, 2) AS prints_m,
    round(sum(toFloat64(price) * size) * 100 / 1e9, 2) AS premium_notional_busd,
    round(toFloat64(sumIf(size, substring(ticker, 13, 1) = 'P')) / toFloat64(sumIf(size, substring(ticker, 13, 1) = 'C')), 2) AS week_put_call_ratio,
    trailing_pc AS trailing_4w_put_call_ratio,
    uniqExact(substring(ticker, 7, 6)) AS expiries_traded,
    concat('$', toString(round(toFloat64(toUInt32OrZero(substring(busiest_t, 14, 8))) / 1000, 2)),
           if(substring(busiest_t, 13, 1) = 'P', ' put', ' call'),
           ', expiry 20', substring(busiest_t, 7, 2), '-', substring(busiest_t, 9, 2), '-', substring(busiest_t, 11, 2)) AS busiest_contract,
    round(friday_close - toFloat64(toUInt32OrZero(substring(busiest_t, 14, 8))) / 1000, 2) AS friday_close_over_busiest_strike,
    round(100.0 * sumIf(size, substring(ticker, 7, 6) = '260710') / sum(size), 1) AS friday_expiry_share_pct,
    back_expiry AS busiest_back_expiry_md,
    round(100.0 * sumIf(size, substring(ticker, 13, 1) = 'C' AND toFloat64(toUInt32OrZero(substring(ticker, 14, 8))) / 1000 > start_close) / sumIf(size, substring(ticker, 13, 1) = 'C'), 1) AS call_vol_strike_above_start_pct
FROM global_markets.options_trades
WHERE startsWith(ticker, 'O:NVDA') AND length(ticker) = 21
  AND sip_timestamp >= toDateTime64('2026-07-06 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-11 00:00:00', 9)