How a near coin flip resolves: average implied probability by checkpoint, split by outcome

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 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('2025-06-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
),
coin_flips AS (
    SELECT ticker AS contract
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND date >= toDate('2025-06-01')
      AND expiration_date <= toDate('2026-06-30')
      AND days_to_expiry BETWEEN 28 AND 32
      AND iv_converged = 1
      AND volume > 0
      AND delta > 0
    GROUP BY contract
    HAVING avg(delta) BETWEEN 0.35 AND 0.65
),
obs AS (
    SELECT g.days_to_expiry AS dte,
           g.delta AS implied_prob,
           if(px.close_px > toFloat64(g.strike_price), 1, 0) AS finished_above
    FROM global_markets.options_greeks AS g
    INNER JOIN coin_flips AS c ON g.ticker = c.contract
    INNER JOIN px ON px.session = g.expiration_date
    WHERE g.underlying_symbol = 'SPY'
      AND g.date >= toDate('2025-06-01')
      AND g.expiration_date <= toDate('2026-06-30')
      AND g.iv_converged = 1
      AND g.volume > 0
      AND g.delta > 0
      AND (g.days_to_expiry BETWEEN 28 AND 32
        OR g.days_to_expiry BETWEEN 19 AND 23
        OR g.days_to_expiry BETWEEN 12 AND 16
        OR g.days_to_expiry BETWEEN 6 AND 8
        OR g.days_to_expiry BETWEEN 3 AND 4
        OR g.days_to_expiry <= 1)
)
SELECT multiIf(dte >= 28, '30 days out',
               dte >= 19, '21 days out',
               dte >= 12, '14 days out',
               dte >= 6, '7 days out',
               dte >= 3, '3 days out',
                         '1 day out') AS checkpoint,
       round(100 * avgIf(implied_prob, finished_above = 1), 1) AS eventual_yes_pct,
       round(100 * avgIf(implied_prob, finished_above = 0), 1) AS eventual_no_pct,
       count() AS contract_days
FROM obs
GROUP BY checkpoint
HAVING countIf(finished_above = 1) > 0 AND countIf(finished_above = 0) > 0
ORDER BY avg(dte) DESC