late_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 marks AS
(
    SELECT
        ticker,
        toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
        anyIf(toFloat64(close),
              toHour(toTimeZone(window_start, 'America/New_York')) = 15
              AND toMinute(toTimeZone(window_start, 'America/New_York')) = 50) AS px_350,
        anyIf(toFloat64(close),
              toHour(toTimeZone(window_start, 'America/New_York')) = 16
              AND toMinute(toTimeZone(window_start, 'America/New_York')) = 0)  AS px_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'KO')
      AND window_start >= '2025-05-01'
      AND window_start <  '2026-06-29'
      AND toHour(toTimeZone(window_start, 'America/New_York')) IN (15, 16)
    GROUP BY ticker, session_date
    HAVING px_350 > 0
       AND px_close > 0
),
drift AS
(
    SELECT
        ticker,
        session_date,
        round(10000 * abs(px_close / px_350 - 1), 1)                  AS drift_bps,
        row_number() OVER (PARTITION BY ticker ORDER BY session_date) AS n
    FROM marks
),
events AS
(
    SELECT
        ticker,
        drift_bps,
        n,
        multiIf(session_date = '2025-06-27', 'Russell recon Jun-2025',
                session_date = '2025-12-19', 'S&P quarterly Dec-2025',
                session_date = '2026-03-20', 'S&P quarterly Mar-2026',
                                             'Russell recon Jun-2026') AS event
    FROM drift
    WHERE session_date IN ('2025-06-27', '2025-12-19', '2026-03-20', '2026-06-26')
)
SELECT
    concat(e.ticker, ' ', e.event)                         AS label,
    e.drift_bps                                            AS event_drift_bps,
    round(quantileDeterministic(0.5)(b.drift_bps, b.n), 1) AS typical_drift_bps
FROM events AS e
INNER JOIN drift AS b ON b.ticker = e.ticker
WHERE b.n >= e.n - 20
  AND b.n <= e.n - 1
GROUP BY e.ticker, e.event, e.drift_bps
ORDER BY event_drift_bps DESC