3,256 answered market questions

WITH
snaps AS
(
    SELECT
        ticker                                                AS contract,
        any(if(upper(substring(toString(option_type), 1, 1)) = 'C', 'call', 'put')) AS opt,
        argMin(abs(delta), abs(toInt32(days_to_expiry) - 30)) AS abs_delta,
        argMin(100 * toFloat64(option_close) / toFloat64(underlying_close),
               abs(toInt32(days_to_expiry) - 30))             AS premium_pct
    FROM global_markets.options_greeks
    WHERE date >= '2024-01-01'
      AND date <  '2026-09-01'
      AND days_to_expiry BETWEEN 27 AND 33
      AND delta != 0
      AND volume > 0
      AND option_close > 0
      AND underlying_close > 0
      AND toDate(expiration_date) < '2026-09-01'
      AND underlying_symbol IN ('AAPL', 'MSFT', 'NVDA', 'AMZN', 'SPY', 'KO', 'JPM', 'XOM')
    GROUP BY contract
    HAVING abs_delta >= 0.05 AND abs_delta < 0.50
),
banded AS
(
    SELECT
        toUInt16(floor(abs_delta * 20)) AS band,
        contract,
        opt,
        premium_pct
    FROM snaps
)
SELECT
    concat(toString(band * 5), ' to ', toString(band * 5 + 5))                              AS delta_band,
    round(quantileDeterministicIf(0.5)(premium_pct, cityHash64(contract), opt = 'call'), 2) AS call_premium_pct,
    round(quantileDeterministicIf(0.5)(premium_pct, cityHash64(contract), opt = 'put'), 2)  AS put_premium_pct,
    count()                                                                                 AS contract_count
FROM banded
GROUP BY band
HAVING countIf(opt = 'call') > 0 AND countIf(opt = 'put') > 0
ORDER BY band