Contracts priced near a coin flip: implied vs realized outcome by name, 2023 through June 2026

WITH px AS (
    SELECT ticker,
           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 IN ('SPY', 'AAPL', 'MSFT', 'NVDA', 'KO', 'XOM')
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2023-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 ticker, session
),
snap AS (
    SELECT underlying_symbol AS symbol,
           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 IN ('SPY', 'AAPL', 'MSFT', 'NVDA', 'KO', 'XOM')
      AND date >= toDate('2023-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 BETWEEN 0.40 AND 0.60
    GROUP BY symbol, contract
)
SELECT snap.symbol AS symbol,
       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.ticker = snap.symbol AND px.session = snap.expiry
GROUP BY symbol
ORDER BY finished_above_pct DESC