The same session scored at five different lookback windows

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
        date,
        avg(toFloat64(implied_volatility)) * 100 AS atm_iv
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'AAPL'
      AND date >= today() - 800
      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 date
),
latest AS
(
    SELECT
        argMax(atm_iv, date) AS iv_now,
        max(date)            AS as_of
    FROM daily
),
windows AS
(
    SELECT
        spec.1 AS lookback_label,
        spec.2 AS lookback_days
    FROM
    (
        SELECT arrayJoin([
            ('1 month', 30),
            ('3 months', 90),
            ('6 months', 180),
            ('12 months', 371),
            ('24 months', 742)
        ]) AS spec
    )
)
SELECT
    w.lookback_label AS lookback_label,
    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,
    count()                                                                               AS observations
FROM daily AS d
CROSS JOIN windows AS w
CROSS JOIN latest AS l
WHERE d.date >= l.as_of - w.lookback_days
GROUP BY w.lookback_label, w.lookback_days, l.iv_now
ORDER BY w.lookback_days