3,256 answered market questions

WITH daily AS
(
    SELECT
        underlying_symbol                        AS symbol,
        date,
        avg(toFloat64(implied_volatility)) * 100 AS atm_iv
    FROM global_markets.options_greeks
    WHERE underlying_symbol IN ('AAPL', 'MSFT', 'NVDA', 'AMZN', 'TSLA', 'SPY', 'QQQ', 'KO')
      AND date >= today() - 371
      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(atm_iv, date) AS iv_now,
        max(date)            AS as_of
    FROM daily
    GROUP BY symbol
)
SELECT
    d.symbol AS symbol,
    round(100 * (l.iv_now - min(d.atm_iv)) / nullIf(max(d.atm_iv) - min(d.atm_iv), 0), 1) AS iv_rank,
    round(100 * countIf(d.atm_iv < l.iv_now) / count(), 1)                                AS iv_percentile,
    round(abs(iv_rank - iv_percentile), 1)                                                AS gap,
    formatDateTime(any(l.as_of), '%b %e, %Y')                                             AS as_of_label
FROM daily AS d
INNER JOIN latest AS l ON l.symbol = d.symbol
GROUP BY d.symbol, l.iv_now
ORDER BY gap DESC