One stock, one session, three definitions of the IV input

WITH contracts AS
(
    SELECT
        date,
        days_to_expiry,
        abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) AS moneyness,
        toFloat64(implied_volatility) * 100                            AS iv_pct
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'AAPL'
      AND date >= today() - 371
      AND iv_converged = 1
      AND volume > 0
),
defs AS
(
    SELECT
        spec.1 AS iv_series,
        spec.2 AS min_dte,
        spec.3 AS max_dte,
        spec.4 AS max_moneyness
    FROM
    (
        SELECT arrayJoin([
            ('Near 30 day ATM', 20, 45, 0.05),
            ('Front month ATM', 1, 19, 0.05),
            ('Wide net, any expiry', 1, 400, 0.30)
        ]) AS spec
    )
),
daily AS
(
    SELECT
        s.iv_series AS iv_series,
        c.date      AS date,
        avg(c.iv_pct) AS atm_iv
    FROM contracts AS c
    CROSS JOIN defs AS s
    WHERE c.days_to_expiry BETWEEN s.min_dte AND s.max_dte
      AND c.moneyness < s.max_moneyness
    GROUP BY iv_series, date
),
latest AS
(
    SELECT
        iv_series,
        argMax(atm_iv, date) AS iv_now
    FROM daily
    GROUP BY iv_series
)
SELECT
    d.iv_series             AS iv_series,
    round(l.iv_now, 2)      AS current_iv_pct,
    round(min(d.atm_iv), 2) AS low_52w_iv_pct,
    round(max(d.atm_iv), 2) AS high_52w_iv_pct,
    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
FROM daily AS d
INNER JOIN latest AS l ON l.iv_series = d.iv_series
GROUP BY d.iv_series, l.iv_now
ORDER BY current_iv_pct DESC