2,170 answered market questions

SELECT
    depth_bucket                            AS itm_depth,
    round(avgIf(vega_f, side = 'call'), 3)  AS call_avg_vega,
    round(avgIf(vega_f, side = 'put'), 3)   AS put_avg_vega,
    countIf(side = 'call')                  AS call_contracts,
    countIf(side = 'put')                   AS put_contracts
FROM
(
    SELECT
        if(delta > 0, 'call', 'put') AS side,
        toFloat64(vega)              AS vega_f,
        if(delta > 0,
           toFloat64(underlying_close) / toFloat64(strike_price) - 1,
           toFloat64(strike_price) / toFloat64(underlying_close) - 1) AS depth,
        multiIf(depth < 0.01, 'under 1%',
                depth < 0.03, '1 to 3%',
                depth < 0.05, '3 to 5%',
                depth < 0.10, '5 to 10%',
                              'over 10%') AS depth_bucket
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'AAPL'
      AND date >= today() - 120
      AND iv_converged = 1
      AND volume > 0
      AND days_to_expiry BETWEEN 3 AND 30
      AND toFloat64(underlying_close) > 0
      AND toFloat64(strike_price) > 0
      AND toFloat64(delta) != 0
      AND depth > 0
)
GROUP BY depth_bucket
HAVING countIf(side = 'call') > 0 AND countIf(side = 'put') > 0
ORDER BY min(depth)