Implied vs realized volatility, six heavily traded US underlyings, twelve months to June 30, 2026

WITH daily AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
           argMax(close, window_start) AS session_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY','QQQ','IWM','AAPL','MSFT','NVDA')
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2025-06-20')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-06-30')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, session_date
),
rets AS (
    SELECT ticker,
           session_date,
           toFloat64(session_close) AS close_px,
           any(toFloat64(session_close)) OVER (PARTITION BY ticker ORDER BY session_date ASC
                                               ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prior_close
    FROM daily
),
realized AS (
    SELECT ticker,
           round(stddevSamp(close_px / prior_close - 1) * sqrt(252) * 100, 1) AS realized_vol_pct,
           count() AS sessions
    FROM rets
    WHERE prior_close > 0
      AND session_date >= toDate('2025-07-01')
    GROUP BY ticker
),
implied AS (
    SELECT underlying_symbol AS underlying,
           round(quantileDeterministic(0.5)(toFloat64(implied_volatility) * 100, cityHash64(ticker)), 1) AS implied_vol_pct
    FROM global_markets.options_greeks
    WHERE underlying_symbol IN ('SPY','QQQ','IWM','AAPL','MSFT','NVDA')
      AND date >= toDate('2025-07-01')
      AND date <= toDate('2026-06-30')
      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 underlying
)
SELECT i.underlying AS ticker,
       i.implied_vol_pct AS implied_vol_pct,
       r.realized_vol_pct AS realized_vol_pct,
       round(i.implied_vol_pct - r.realized_vol_pct, 1) AS premium_pct
FROM implied AS i
INNER JOIN realized AS r ON i.underlying = r.ticker
WHERE r.sessions >= 200
ORDER BY premium_pct DESC