3,256 answered market questions

WITH chain AS
(
    SELECT
        expiration_date,
        toFloat64(strike_price)          AS strike,
        toFloat64(strike_price) + 5      AS next_strike,
        avg(toFloat64(option_close))     AS premium,
        avg(toFloat64(underlying_close)) AS spot,
        sum(volume)                      AS contracts
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'AAPL'
      AND lower(option_type) IN ('call', 'c')
      AND date >= today() - 30
      AND date = (
              SELECT max(date)
              FROM global_markets.options_greeks
              WHERE underlying_symbol = 'AAPL'
                AND date >= today() - 30
          )
      AND days_to_expiry BETWEEN 20 AND 45
      AND iv_converged = 1
      AND volume > 0
    GROUP BY expiration_date, strike, next_strike
),
busiest AS
(
    SELECT expiration_date
    FROM chain
    GROUP BY expiration_date
    ORDER BY sum(contracts) DESC
    LIMIT 1
)
SELECT
    concat('$', toString(round(lo.strike, 2)), ' / $', toString(round(hi.strike, 2))) AS spread_label,
    round(lo.premium - hi.premium, 2)                                                 AS max_loss_per_share,
    round(5 - (lo.premium - hi.premium), 2)                                           AS max_gain_per_share
FROM chain AS lo
INNER JOIN chain AS hi
    ON lo.expiration_date = hi.expiration_date
   AND lo.next_strike = hi.strike
WHERE lo.expiration_date IN (SELECT expiration_date FROM busiest)
  AND lo.strike >= lo.spot * 0.97
  AND lo.strike <= lo.spot * 1.08
ORDER BY lo.strike