The 15 to 25 delta band at three horizons: implied versus realized in the money share

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
        underlying_symbol     AS sym,
        toDate(date)          AS d,
        any(underlying_close) AS close_at_expiry
    FROM global_markets.options_greeks
    WHERE date >= '2024-01-01'
      AND date <  '2026-09-01'
      AND underlying_close > 0
      AND underlying_symbol IN ('AAPL', 'MSFT', 'NVDA', 'AMZN', 'SPY', 'KO', 'JPM', 'XOM')
    GROUP BY sym, d
),
obs AS
(
    SELECT
        ticker                  AS contract,
        underlying_symbol       AS sym,
        if(upper(substring(toString(option_type), 1, 1)) = 'C', 'call', 'put') AS opt,
        strike_price            AS strike,
        toDate(expiration_date) AS expiry,
        abs(delta)              AS abs_delta,
        toInt32(days_to_expiry) AS dte,
        multiIf(days_to_expiry BETWEEN 27 AND 33, 30,
                days_to_expiry BETWEEN 12 AND 16, 14,
                7)              AS horizon
    FROM global_markets.options_greeks
    WHERE date >= '2024-01-01'
      AND date <  '2026-09-01'
      AND delta != 0
      AND volume > 0
      AND toDate(expiration_date) < '2026-09-01'
      AND underlying_symbol IN ('AAPL', 'MSFT', 'NVDA', 'AMZN', 'SPY', 'KO', 'JPM', 'XOM')
      AND (days_to_expiry BETWEEN 5 AND 9
           OR days_to_expiry BETWEEN 12 AND 16
           OR days_to_expiry BETWEEN 27 AND 33)
),
snaps AS
(
    SELECT
        contract,
        horizon,
        any(sym)                              AS sym,
        any(opt)                              AS opt,
        any(strike)                           AS strike,
        any(expiry)                           AS expiry,
        argMin(abs_delta, abs(dte - horizon)) AS abs_delta
    FROM obs
    GROUP BY contract, horizon
    HAVING abs_delta >= 0.15 AND abs_delta < 0.25
),
joined AS
(
    SELECT
        s.horizon   AS horizon,
        s.opt       AS opt,
        s.abs_delta AS abs_delta,
        if(s.opt = 'call',
           p.close_at_expiry > s.strike,
           p.close_at_expiry < s.strike) AS finished_itm
    FROM snaps AS s
    INNER JOIN px AS p ON p.sym = s.sym AND p.d = s.expiry
)
SELECT
    concat(toString(horizon), ' days out')                                        AS snapshot,
    round(100 * avg(abs_delta), 1)                                                AS implied_pct,
    round(100 * countIf(finished_itm AND opt = 'call') / countIf(opt = 'call'), 1) AS call_itm_pct,
    round(100 * countIf(finished_itm AND opt = 'put') / countIf(opt = 'put'), 1)   AS put_itm_pct,
    count()                                                                       AS contract_count
FROM joined
GROUP BY horizon
HAVING countIf(opt = 'call') > 0 AND countIf(opt = 'put') > 0
ORDER BY horizon DESC