3,256 answered market questions

WITH shorts AS
(
    SELECT
        date                                                   AS entry_date,
        expiration_date                                        AS expiry,
        if(delta < 0, 'put', 'call')                           AS side,
        argMin(toFloat64(strike_price), abs(abs(delta) - 0.16)) AS short_strike
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND date >= '2026-01-02'
      AND date <  '2026-08-01'
      AND iv_converged = 1
      AND volume > 100
      AND days_to_expiry BETWEEN 28 AND 35
      AND abs(delta) BETWEEN 0.13 AND 0.19
    GROUP BY entry_date, expiry, side
),
tape AS
(
    SELECT
        date,
        toFloat64(high)  AS high,
        toFloat64(low)   AS low,
        toFloat64(close) AS close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2026-01-02'
      AND date <= '2026-09-30'
),
outcomes AS
(
    SELECT
        s.entry_date            AS entry_date,
        s.expiry                AS expiry,
        s.side                  AS side,
        s.short_strike          AS short_strike,
        max(t.high)             AS path_high,
        min(t.low)              AS path_low,
        argMax(t.close, t.date) AS final_close
    FROM shorts AS s
    CROSS JOIN tape AS t
    WHERE t.date >  s.entry_date
      AND t.date <= s.expiry
    GROUP BY entry_date, expiry, side, short_strike
)
SELECT
    formatDateTime(toStartOfMonth(entry_date), '%Y-%m') AS month,
    round(100 * avg(if(side = 'put', path_low <= short_strike,
                                     path_high >= short_strike)), 1) AS touched_strike_pct,
    round(100 * avg(if(side = 'put', final_close < short_strike,
                                     final_close > short_strike)), 1) AS finished_itm_pct,
    round(avg(if(side = 'put', path_low <= short_strike, path_high >= short_strike))
        / avg(if(side = 'put', final_close < short_strike, final_close > short_strike)), 2)
                                                                      AS touch_to_itm_ratio,
    count()                                                           AS tracked_count
FROM outcomes
GROUP BY month
HAVING countIf(if(side = 'put', final_close < short_strike, final_close > short_strike)) > 0
ORDER BY month