SPY Friday closes against the whole dollar strike grid, by year

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(100 * countIf(px_close != floor(px_close)) / count(), 1)      AS between_strikes_pct,
    round(100 * countIf(floor(px_close) != floor(px_3pm)) / count(), 1) AS crossed_strike_pct,
    round(100 * countIf(least(px_close - floor(px_close), floor(px_close) + 1 - px_close) <= 0.10) / count(), 1) AS within_10_cents_pct
FROM day_marks
WHERE toDayOfWeek(session_date) = 5
GROUP BY year
ORDER BY year