IV rank against IV percentile: the latest reading inside each ticker's 52-week range

WITH atm AS (
    SELECT underlying_symbol AS symbol,
           date,
           avg(implied_volatility) * 100 AS iv_pct
    FROM global_markets.options_greeks
    WHERE underlying_symbol IN ('SPY', 'KO', 'AAPL', 'MSFT', 'NVDA', 'TSLA', 'COIN', 'MSTR')
      AND date >= toDate('2025-08-01')
      AND date <= toDate('2026-07-31')
      AND iv_converged = 1
      AND volume > 0
      AND underlying_close > 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(iv_pct, date) AS latest_iv
    FROM atm
    GROUP BY symbol
)
SELECT a.symbol AS symbol,
       round(any(l.latest_iv), 1) AS latest_iv_pct,
       round(min(a.iv_pct), 1) AS low_52w_iv_pct,
       round(max(a.iv_pct), 1) AS high_52w_iv_pct,
       round(100 * (any(l.latest_iv) - min(a.iv_pct)) / (max(a.iv_pct) - min(a.iv_pct)), 1) AS iv_rank,
       round(100 * countIf(a.iv_pct < l.latest_iv) / count(), 1) AS iv_percentile
FROM atm AS a
INNER JOIN latest AS l ON a.symbol = l.symbol
GROUP BY a.symbol
HAVING max(a.iv_pct) > min(a.iv_pct) AND count() >= 100
ORDER BY iv_rank DESC