The seasoning curve: regular-hours spread by session, per-update and time-weighted

SELECT
    session,
    quote_updates,
    invalid_dropped,
    med_spread_cents,
    tw_spread_cents,
    med_spread_bps,
    tw_spread_bps,
    round(100 * (tw_spread_bps / min(tw_spread_bps) OVER () - 1), 0) AS pct_above_tightest_session
FROM (
    SELECT
        session,
        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(sumIf(spread * dt, valid AND dt > 0) / sumIf(dt, valid AND dt > 0) * 100, 1) AS tw_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 session, spread, mid, valid, ts_us,
               greatest(if(next_us = 0, end_us, least(next_us, end_us)) - ts_us, 0) AS dt
        FROM (
            SELECT
                toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS session,
                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(concat(toString(toDate(toTimeZone(sip_timestamp, 'America/New_York'))), ' 20:00:00'), 6, 'UTC')) AS end_us,
                leadInFrame(toUnixTimestamp64Micro(sip_timestamp)) OVER (PARTITION BY toDate(toTimeZone(sip_timestamp, 'America/New_York')) 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 = 'SPCX'
              AND sip_timestamp >= toDateTime64('2026-06-12 15:46:00', 9)
              AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)
              AND (toHour(sip_timestamp) * 60 + toMinute(sip_timestamp)) BETWEEN 810 AND 1199
        )
    )
    GROUP BY session
)
ORDER BY session