How far SPY travels between 3:00 p.m. ET and the Friday close

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 bars AS (
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York'))        AS session_date,
        toHour(toTimeZone(window_start, 'America/New_York')) * 60
          + toMinute(toTimeZone(window_start, 'America/New_York'))  AS et_minute,
        toFloat64(close)                                            AS px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= '2022-01-01 00:00:00'
      AND window_start <  '2026-09-01 00:00:00'
),
day_marks AS (
    SELECT
        session_date,
        anyIf(px, et_minute = 900) AS px_3pm,
        argMax(px, et_minute)      AS px_close
    FROM bars
    WHERE et_minute >= 570
      AND et_minute <= 959
    GROUP BY session_date
    HAVING px_3pm > 0
       AND px_close > 0
       AND max(et_minute) >= 955
)
SELECT
    toString(toYear(session_date)) AS year,
    count()                        AS expiration_count,
    round(quantileDeterministic(0.5)(100 * abs(px_close - px_3pm) / px_3pm, toUInt32(toUnixTimestamp(session_date))), 3) AS median_final_hour_move_pct,
    round(quantileDeterministic(0.9)(100 * abs(px_close - px_3pm) / px_3pm, toUInt32(toUnixTimestamp(session_date))), 3) AS p90_final_hour_move_pct
FROM day_marks
WHERE toDayOfWeek(session_date) = 5
GROUP BY year
ORDER BY year