SPY's option quotes on July 7: the whole root, and the near-the-money same-day touch through the air pocket against a midday control

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH
    (
        SELECT count()
        FROM global_markets.cache_stocks_quotes
        WHERE toYYYYMMDD(sip_timestamp) = 20260707
    ) AS jul7_stock_quote_rows,
    (
        SELECT round(toFloat64(avg(close)), 2)
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= '2026-07-07 14:36:00' AND window_start < '2026-07-07 14:48:00'
    ) AS spy_pocket_price,
    (
        SELECT round(toFloat64(avg(close)), 2)
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= '2026-07-07 17:30:00' AND window_start < '2026-07-07 17:42:00'
    ) AS spy_midday_price
SELECT
    round(count() / 1e6, 2) AS spy_option_updates_m,
    round(100.0 * count() / jul7_stock_quote_rows, 1) AS pct_of_whole_equity_quote_tape,
    uniqExact(ticker) AS spy_contracts_quoted,
    round(countIf(substring(ticker, -15, 6) = '260707') / 1e6, 2) AS same_day_updates_m,
    spy_pocket_price AS spy_price_in_pocket,
    spy_midday_price AS spy_price_midday,
    round(quantileExactIf(0.5)(toFloat64(ask_price - bid_price),
        bid_price > 0 AND ask_price >= bid_price
        AND substring(ticker, -15, 6) = '260707'
        AND abs(toFloat64(toUInt32OrZero(substring(ticker, -8))) / 1000 - spy_pocket_price) <= 2
        AND sip_timestamp >= '2026-07-07 14:36:00' AND sip_timestamp < '2026-07-07 14:48:00'), 3) AS pocket_atm_spread_dollars,
    round(quantileExactIf(0.5)(toFloat64(ask_price - bid_price),
        bid_price > 0 AND ask_price >= bid_price
        AND substring(ticker, -15, 6) = '260707'
        AND abs(toFloat64(toUInt32OrZero(substring(ticker, -8))) / 1000 - spy_midday_price) <= 2
        AND sip_timestamp >= '2026-07-07 17:30:00' AND sip_timestamp < '2026-07-07 17:42:00'), 3) AS midday_atm_spread_dollars,
    round(pocket_atm_spread_dollars - midday_atm_spread_dollars, 3) AS atm_spread_delta_dollars,
    round(countIf(substring(ticker, -15, 6) = '260707'
        AND abs(toFloat64(toUInt32OrZero(substring(ticker, -8))) / 1000 - spy_pocket_price) <= 2
        AND sip_timestamp >= '2026-07-07 14:36:00' AND sip_timestamp < '2026-07-07 14:48:00') / 1e3, 1) AS pocket_atm_updates_k,
    round(countIf(substring(ticker, -15, 6) = '260707'
        AND abs(toFloat64(toUInt32OrZero(substring(ticker, -8))) / 1000 - spy_midday_price) <= 2
        AND sip_timestamp >= '2026-07-07 17:30:00' AND sip_timestamp < '2026-07-07 17:42:00') / 1e3, 1) AS midday_atm_updates_k,
    round(pocket_atm_updates_k / midday_atm_updates_k, 1) AS atm_quote_rate_ratio,
    countIf(NOT (bid_price > 0 AND ask_price >= bid_price)
        AND substring(ticker, -15, 6) = '260707') AS dropped_invalid_same_day_quotes
FROM global_markets.cache_options_quotes
WHERE ticker >= 'O:SPY26' AND ticker < 'O:SPY27'
  AND sip_timestamp >= '2026-07-07 13:30:00' AND sip_timestamp < '2026-07-07 20:00:00'
HAVING pocket_atm_updates_k > 0 AND midday_atm_updates_k > 0