June 30 anchors: a mega-cap, SPCX, and a thin small-cap, same session, same math

SELECT
    ticker,
    count() AS quote_updates,
    countIf(NOT valid) AS invalid_dropped,
    round(quantileDeterministicIf(0.5)(spread, toUInt64(ts_us), valid) * 100, 1) AS med_spread_cents,
    round(quantileDeterministicIf(0.5)(spread / mid * 10000, toUInt64(ts_us), valid), 2) AS med_spread_bps,
    round(sumIf(spread / mid * dt, valid AND dt > 0) / sumIf(dt, valid AND dt > 0) * 10000, 2) AS tw_spread_bps
FROM (
    SELECT ticker, spread, mid, valid, ts_us,
           greatest(if(next_us = 0, end_us, least(next_us, end_us)) - ts_us, 0) AS dt
    FROM (
        SELECT
            ticker,
            toFloat64(ask_price) - toFloat64(bid_price) AS spread,
            (toFloat64(ask_price) + toFloat64(bid_price)) / 2 AS mid,
            (bid_price > 0 AND ask_price > 0 AND ask_price >= bid_price) AS valid,
            toUnixTimestamp64Micro(sip_timestamp) AS ts_us,
            toUnixTimestamp64Micro(toDateTime64('2026-06-30 20:00:00', 6, 'UTC')) AS end_us,
            leadInFrame(toUnixTimestamp64Micro(sip_timestamp)) OVER (PARTITION BY ticker ORDER BY sip_timestamp ASC, sequence_number ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS next_us
        FROM global_markets.cache_stocks_quotes
        WHERE ticker IN ('SPCX', 'AAPL', 'NATH')
          AND sip_timestamp >= toDateTime64('2026-06-30 13:30:00', 9)
          AND sip_timestamp < toDateTime64('2026-06-30 20:00:00', 9)
    )
)
GROUP BY ticker
ORDER BY ticker