Implied probability vs what happened: SPY calls 30 days from expiry, 2022 through June 2026

WITH px AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS session,
           argMax(toFloat64(close), window_start) AS close_px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2022-01-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-06-30')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY session
),
snap AS (
    SELECT ticker AS contract,
           argMin(delta, abs(days_to_expiry - 30)) AS implied_prob,
           toFloat64(any(strike_price)) AS strike,
           any(expiration_date) AS expiry
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND date >= toDate('2022-01-01')
      AND expiration_date <= toDate('2026-06-30')
      AND days_to_expiry BETWEEN 25 AND 35
      AND iv_converged = 1
      AND volume > 0
      AND delta > 0.02
      AND delta < 0.98
    GROUP BY contract
)
SELECT multiIf(implied_prob < 0.10, '2-10%',
               implied_prob < 0.20, '10-20%',
               implied_prob < 0.30, '20-30%',
               implied_prob < 0.40, '30-40%',
               implied_prob < 0.50, '40-50%',
               implied_prob < 0.60, '50-60%',
               implied_prob < 0.70, '60-70%',
               implied_prob < 0.80, '70-80%',
               implied_prob < 0.90, '80-90%',
                                    '90-98%') AS implied_bucket,
       count() AS contracts,
       round(100 * avg(implied_prob), 1) AS implied_prob_pct,
       round(100 * avg(close_px > strike), 1) AS finished_above_pct
FROM snap
INNER JOIN px ON px.session = snap.expiry
GROUP BY implied_bucket
ORDER BY implied_prob_pct