Implied volatility beside the movement each stock actually delivered over the prior 30 sessions

WITH daily AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           argMax(close, window_start) AS px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('KO','JNJ','SPY','QQQ','AAPL','MSFT','NVDA','TSLA','PLTR','COIN','MSTR')
      AND toDate(toTimeZone(window_start, 'America/New_York')) BETWEEN toDate('2026-05-20') AND toDate('2026-07-28')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, d
),
rets AS (
    SELECT ticker, d,
           log(px / any(px) OVER (PARTITION BY ticker ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)) AS r
    FROM daily
),
realized AS (
    SELECT ticker, 100 * stddevSamp(r) * sqrt(252) AS rv
    FROM rets
    WHERE r IS NOT NULL AND d > toDate('2026-06-12')
    GROUP BY ticker
    HAVING count() >= 20
),
implied AS (
    SELECT underlying_symbol AS ticker,
           100 * quantileExact(0.5)(implied_volatility) AS iv
    FROM global_markets.options_greeks
    WHERE date = '2026-07-28'
      AND underlying_symbol IN ('KO','JNJ','SPY','QQQ','AAPL','MSFT','NVDA','TSLA','PLTR','COIN','MSTR')
      AND iv_converged AND implied_volatility BETWEEN 0.02 AND 5
      AND abs(strike_price / underlying_close - 1) <= 0.05
      AND expiration_date BETWEEN date + 20 AND date + 60
    GROUP BY ticker
)
SELECT implied.ticker AS ticker,
       round(implied.iv, 1) AS implied_vol_pct,
       round(realized.rv, 1) AS realized_vol_30d_pct,
       round(implied.iv - realized.rv, 1) AS iv_minus_realized
FROM implied INNER JOIN realized ON implied.ticker = realized.ticker
ORDER BY iv_minus_realized DESC