SPY options median spread by expiration date, near-the-money strikes only

WITH opts AS (
    SELECT
        toFloat64(ask_price) - toFloat64(bid_price) AS width,
        (toFloat64(ask_price) + toFloat64(bid_price)) / 2 AS mid,
        substring(ticker, -15, 6) AS expiry_code,
        toUInt32OrZero(substring(ticker, -8)) / 1000 AS strike,
        cityHash64(ticker, sip_timestamp) AS det
    FROM global_markets.cache_options_quotes
    WHERE sip_timestamp >= '2026-07-02 13:30:00' AND sip_timestamp < '2026-07-02 20:00:00'
      AND ticker >= 'O:SPY26' AND ticker < 'O:SPY27'
      AND bid_price > 0 AND ask_price > 0 AND ask_price > bid_price
      AND strike BETWEEN 730 AND 760
)
SELECT
    expiry_code,
    round(count() / 1e6, 1) AS quote_updates_millions,
    round(quantileDeterministic(0.5)(width / mid * 10000, det), 2) AS median_spread_bps,
    round(quantileDeterministic(0.5)(width * 100, det), 2) AS median_width_cents
FROM opts
GROUP BY expiry_code
HAVING count() >= 1000000
ORDER BY expiry_code ASC