persistence

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
    weeks AS
    (
        SELECT toMonday(date) AS wk, max(date) AS session
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'SPY'
          AND date >= today() - 84
        GROUP BY wk
    ),
    legs AS
    (
        SELECT
            toMonday(g.date)                          AS wk,
            g.expiration_date                         AS expiration_date,
            toFloat64(g.strike_price)                 AS k,
            if(toFloat64(g.delta) < 0, 'put', 'call') AS side,
            max(g.days_to_expiry)                     AS dte,
            avg(toFloat64(g.option_close))            AS px,
            avg(toFloat64(g.delta))                   AS d
        FROM global_markets.options_greeks AS g
        INNER JOIN weeks AS w ON g.date = w.session
        WHERE g.underlying_symbol = 'SPY'
          AND g.date >= today() - 84
          AND g.iv_converged = 1
          AND g.volume > 0
          AND toFloat64(g.option_close) > 0
          AND toUInt32(round(toFloat64(g.strike_price) * 100)) % 500 = 0
        GROUP BY wk, expiration_date, k, side
    ),
    verticals AS
    (
        SELECT
            s.wk              AS wk,
            s.wk + 7          AS next_wk,
            s.expiration_date AS expiration_date,
            s.side            AS side,
            s.k               AS short_k,
            s.dte             AS dte,
            s.px - l.px       AS credit
        FROM legs AS s
        INNER JOIN legs AS l
            ON s.wk = l.wk AND s.expiration_date = l.expiration_date AND s.side = l.side
        WHERE ((s.side = 'put'  AND s.d BETWEEN -0.20 AND -0.12 AND abs(l.k - (s.k - 5)) < 0.01)
            OR (s.side = 'call' AND s.d BETWEEN  0.12 AND  0.20 AND abs(l.k - (s.k + 5)) < 0.01))
    ),
    condors AS
    (
        SELECT
            p.wk                                                                AS wk,
            p.next_wk                                                           AS next_wk,
            p.expiration_date                                                   AS expiration_date,
            p.short_k                                                           AS short_put,
            c.short_k                                                           AS short_call,
            p.dte                                                               AS dte,
            round(100 * (p.credit + c.credit) / (5 - (p.credit + c.credit)), 1) AS credit_pct
        FROM verticals AS p
        INNER JOIN verticals AS c
            ON p.wk = c.wk AND p.expiration_date = c.expiration_date
        WHERE p.side = 'put'
          AND c.side = 'call'
          AND p.credit + c.credit BETWEEN 0.05 AND 4.0
    )
SELECT
    toString(a.wk)                                        AS week,
    formatDateTime(a.wk, '%b %e')                         AS week_of,
    count()                                               AS candidates,
    countIf(b.credit_pct >= 20)                           AS still_clearing,
    round(100 * countIf(b.credit_pct >= 20) / count(), 0) AS survival_pct
FROM condors AS a
LEFT JOIN condors AS b
    ON a.next_wk = b.wk
   AND a.expiration_date = b.expiration_date
   AND a.short_put = b.short_put
   AND a.short_call = b.short_call
WHERE a.dte BETWEEN 25 AND 45
  AND a.credit_pct >= 20
  AND a.next_wk <= toMonday((
        SELECT max(date)
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'SPY'
      ))
GROUP BY a.wk
ORDER BY a.wk