knobs

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
    (
        SELECT max(date)
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'SPY'
    ) AS snapshot,
    (
        SELECT expiration_date
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'SPY'
          AND date = snapshot
          AND iv_converged = 1
          AND volume > 0
          AND days_to_expiry BETWEEN 25 AND 45
        GROUP BY expiration_date
        ORDER BY sum(volume) DESC
        LIMIT 1
    ) AS target_expiry,
    chain AS
    (
        SELECT
            toFloat64(strike_price)                 AS k,
            if(toFloat64(delta) < 0, 'put', 'call') AS side,
            avg(toFloat64(option_close))            AS px,
            avg(toFloat64(delta))                   AS d
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'SPY'
          AND date = snapshot
          AND expiration_date = target_expiry
          AND iv_converged = 1
          AND volume > 0
          AND toFloat64(option_close) > 0
          AND toUInt32(round(toFloat64(strike_price) * 100)) % 500 = 0
        GROUP BY k, side
    ),
    put_spreads AS
    (
        SELECT s.d AS short_delta, s.px - l.px AS credit
        FROM chain AS s
        CROSS JOIN chain AS l
        WHERE s.side = 'put' AND l.side = 'put'
          AND s.d BETWEEN -0.25 AND -0.10
          AND abs(l.k - (s.k - 5)) < 0.01
    ),
    call_spreads AS
    (
        SELECT s.d AS short_delta, s.px - l.px AS credit
        FROM chain AS s
        CROSS JOIN chain AS l
        WHERE s.side = 'call' AND l.side = 'call'
          AND s.d BETWEEN 0.10 AND 0.25
          AND abs(l.k - (s.k + 5)) < 0.01
    )
SELECT
    concat(toString(f.min_ratio), '%')                          AS credit_floor,
    countIf(cand.credit_pct >= f.min_ratio AND cand.tight = 1)  AS tight_band,
    countIf(cand.credit_pct >= f.min_ratio)                     AS wide_band,
    formatDateTime(snapshot, '%b %e, %Y')                       AS as_of
FROM
(
    SELECT
        round(100 * (p.credit + c.credit) / (5 - (p.credit + c.credit)), 1) AS credit_pct,
        if((abs(p.short_delta) BETWEEN 0.12 AND 0.20)
           AND (c.short_delta BETWEEN 0.12 AND 0.20), 1, 0)                 AS tight
    FROM put_spreads AS p
    CROSS JOIN call_spreads AS c
    WHERE p.credit + c.credit BETWEEN 0.05 AND 4.0
) AS cand
CROSS JOIN
(
    SELECT arrayJoin([10, 20, 30, 40, 50]) AS min_ratio
) AS f
GROUP BY f.min_ratio
ORDER BY f.min_ratio