spread_ladder

SELECT
    concat(toString(a.strike), '/', toString(b.strike))   AS spread,
    toUInt32(round((a.px - b.px) * 100))                  AS max_loss_usd,
    toUInt32(round((5 - (a.px - b.px)) * 100))            AS max_profit_usd
FROM
(
    SELECT
        toFloat64(strike_price)                                AS strike,
        toInt32(round(toFloat64(strike_price) * 100) + 500)    AS k_plus_5,
        toFloat64(max(option_close))                           AS px,
        toFloat64(max(underlying_close))                       AS spot
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'AAPL'
      AND toDate(date) = '2025-05-19'
      AND toDate(expiration_date) = '2025-06-20'
      AND lower(toString(option_type)) IN ('c', 'call')
      AND iv_converged = 1
      AND volume > 0
    GROUP BY strike_price
) AS a
INNER JOIN
(
    SELECT
        toFloat64(strike_price)                                AS strike,
        toInt32(round(toFloat64(strike_price) * 100))          AS k,
        toFloat64(max(option_close))                           AS px
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'AAPL'
      AND toDate(date) = '2025-05-19'
      AND toDate(expiration_date) = '2025-06-20'
      AND lower(toString(option_type)) IN ('c', 'call')
      AND iv_converged = 1
      AND volume > 0
    GROUP BY strike_price
) AS b ON a.k_plus_5 = b.k
WHERE a.strike >= a.spot
  AND a.px > b.px
  AND a.px - b.px < 5
ORDER BY a.strike
LIMIT 8