Iron Condor Screener from the SQL API

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
SELECT
    toString(toUInt32(toFloat64(strike_price)))              AS strike,
    round(avgIf(toFloat64(delta), toFloat64(delta) < 0), 3)  AS put_delta,
    round(avgIf(toFloat64(delta), toFloat64(delta) > 0), 3)  AS call_delta,
    formatDateTime(target_expiry, '%b %e, %Y')               AS expiry,
    formatDateTime(snapshot, '%b %e, %Y')                    AS as_of
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
  AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.06
GROUP BY strike_price
HAVING countIf(toFloat64(delta) < 0) > 0
   AND countIf(toFloat64(delta) > 0) > 0
ORDER BY strike_price

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.k AS short_k, l.k AS long_k, 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.20 AND -0.12
          AND abs(l.k - (s.k - 5)) < 0.01
    ),
    call_spreads AS
    (
        SELECT s.k AS short_k, l.k AS long_k, 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.12 AND 0.20
          AND abs(l.k - (s.k + 5)) < 0.01
    )
SELECT
    concat(toString(toUInt32(p.short_k)), '/', toString(toUInt32(p.long_k)), ' put + ',
           toString(toUInt32(c.short_k)), '/', toString(toUInt32(c.long_k)), ' call') AS structure,
    concat(toString(round(p.short_delta, 2)), ' / ',
           toString(round(c.short_delta, 2)))                                         AS short_deltas,
    round(p.credit + c.credit, 2)                                                     AS net_credit,
    round(5 - (p.credit + c.credit), 2)                                               AS max_loss,
    round(100 * (p.credit + c.credit) / (5 - (p.credit + c.credit)), 1)               AS credit_pct,
    formatDateTime(target_expiry, '%b %e, %Y')                                        AS expiry,
    formatDateTime(snapshot, '%b %e, %Y')                                             AS as_of
FROM put_spreads AS p
CROSS JOIN call_spreads AS c
WHERE p.credit + c.credit BETWEEN 0.05 AND 4.0
  AND 100 * (p.credit + c.credit) / (5 - (p.credit + c.credit)) >= 20
ORDER BY credit_pct DESC
LIMIT 12

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

WITH
    daily AS
    (
        SELECT
            underlying_symbol                                  AS symbol,
            date,
            round(100 * avg(toFloat64(implied_volatility)), 2) AS iv_pct
        FROM global_markets.options_greeks
        WHERE underlying_symbol IN ('SPY', 'QQQ', 'AAPL', 'MSFT', 'NVDA', 'KO')
          AND date >= today() - 400
          AND iv_converged = 1
          AND volume > 0
          AND days_to_expiry BETWEEN 20 AND 45
          AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.05
        GROUP BY symbol, date
    ),
    latest AS
    (
        SELECT
            symbol,
            argMax(iv_pct, date) AS current_iv,
            max(date)            AS snapshot
        FROM daily
        GROUP BY symbol
    )
SELECT
    l.symbol                                                    AS symbol,
    round(l.current_iv, 1)                                      AS current_iv_pct,
    round(100 * countIf(d.iv_pct <= l.current_iv) / count(), 0) AS iv_percentile,
    formatDateTime(l.snapshot, '%b %e, %Y')                     AS as_of
FROM latest AS l
INNER JOIN daily AS d ON d.symbol = l.symbol
GROUP BY l.symbol, l.current_iv, l.snapshot
ORDER BY iv_percentile DESC

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