mid_drift

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 grid AS
(
    SELECT
        intDiv(toUnixTimestamp64Milli(sip_timestamp), 100)                       AS slot,
        argMax((toFloat64(bid_price) + toFloat64(ask_price)) / 2, sip_timestamp) AS mid
    FROM global_markets.cache_stocks_quotes
    WHERE ticker = 'AAPL'
      AND sip_timestamp >= '2026-09-15 14:00:00'
      AND sip_timestamp <  '2026-09-15 15:00:00'
      AND bid_price > 0
      AND ask_price > bid_price
    GROUP BY slot
),
lagged AS
(
    SELECT
        mid,
        lagInFrame(mid, 1)  OVER w AS mid_100ms_ago,
        lagInFrame(mid, 5)  OVER w AS mid_500ms_ago,
        lagInFrame(mid, 10) OVER w AS mid_1s_ago,
        lagInFrame(mid, 50) OVER w AS mid_5s_ago
    FROM grid
    WINDOW w AS (ORDER BY slot ASC ROWS BETWEEN 50 PRECEDING AND CURRENT ROW)
),
moves AS
(
    SELECT *
    FROM lagged
    WHERE mid_100ms_ago > 0 AND mid_500ms_ago > 0 AND mid_1s_ago > 0 AND mid_5s_ago > 0
)
SELECT
    horizon.1                                        AS latency_horizon,
    round(10000 * avg(abs(horizon.2)) / avg(mid), 3) AS avg_mid_move_bps
FROM moves
ARRAY JOIN
[
    ('0.1 s', mid - mid_100ms_ago),
    ('0.5 s', mid - mid_500ms_ago),
    ('1.0 s', mid - mid_1s_ago),
    ('5.0 s', mid - mid_5s_ago)
] AS horizon
GROUP BY latency_horizon
ORDER BY avg_mid_move_bps ASC