Percentile range of daily ATM implied volatility, five year window

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH
    daily AS
    (
        SELECT
            underlying_symbol                        AS sym,
            date                                     AS d,
            avg(toFloat64(implied_volatility)) * 100 AS atm_iv
        FROM global_markets.options_greeks
        WHERE underlying_symbol IN ('GLD', 'SPY')
          AND date >= '2021-09-01'
          AND date <  '2026-09-01'
          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 sym, d
    ),
    curves AS
    (
        SELECT
            sym,
            quantilesDeterministic(0.05, 0.25, 0.5, 0.75, 0.95)(atm_iv, toUInt64(d)) AS qs
        FROM daily
        GROUP BY sym
    )
SELECT
    ['5th', '25th', '50th', '75th', '95th'][idx] AS percentile,
    round(anyIf(qs, sym = 'GLD')[idx], 2)        AS gld_iv_pct,
    round(anyIf(qs, sym = 'SPY')[idx], 2)        AS spy_iv_pct
FROM curves
ARRAY JOIN [1, 2, 3, 4, 5] AS idx
GROUP BY idx
ORDER BY idx