Implied vs realized: median 30-day expected move and what six stocks did next

WITH obs AS (
    SELECT underlying_symbol AS ticker,
           date AS obs_date,
           avg(toFloat64(implied_volatility)) AS iv,
           avg(toFloat64(underlying_close)) AS spot
    FROM global_markets.options_greeks
    WHERE underlying_symbol IN ('NVDA', 'AAPL', 'MSFT', 'XOM', 'SPY', 'KO')
      AND date BETWEEN toDate('2024-06-12') AND toDate('2026-06-17')
      AND toDayOfWeek(date) = 3
      AND iv_converged = 1
      AND volume > 0
      AND days_to_expiry BETWEEN 25 AND 40
      AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.05
    GROUP BY ticker, obs_date
    HAVING count() >= 4 AND avg(toFloat64(implied_volatility)) > 0.01
),
px AS (
    SELECT underlying_symbol AS ticker,
           date AS px_date,
           avg(toFloat64(underlying_close)) AS close_px
    FROM global_markets.options_greeks
    WHERE underlying_symbol IN ('NVDA', 'AAPL', 'MSFT', 'XOM', 'SPY', 'KO')
      AND date BETWEEN toDate('2024-06-12') AND toDate('2026-07-25')
      AND days_to_expiry BETWEEN 5 AND 60
      AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.02
    GROUP BY ticker, px_date
),
moves AS (
    SELECT obs.ticker AS ticker,
           obs.obs_date AS obs_date,
           any(obs.iv) AS iv,
           any(obs.spot) AS spot,
           argMin(px.close_px, px.px_date) AS end_px,
           min(dateDiff('day', obs.obs_date, px.px_date)) AS elapsed_days
    FROM obs
    INNER JOIN px ON obs.ticker = px.ticker
    WHERE px.px_date >= addDays(obs.obs_date, 30)
      AND px.px_date <= addDays(obs.obs_date, 36)
    GROUP BY ticker, obs_date
)
SELECT ticker,
       count() AS windows_tested,
       round(quantileDeterministic(0.5)(100 * iv * sqrt(elapsed_days / 365), cityHash64(obs_date)), 2) AS implied_move_pct,
       round(quantileDeterministic(0.5)(100 * abs(end_px / spot - 1), cityHash64(obs_date)), 2) AS realized_move_pct,
       round(100 * countIf(abs(end_px / spot - 1) > iv * sqrt(elapsed_days / 365)) / count(), 1) AS realized_over_implied_pct
FROM moves
GROUP BY ticker
ORDER BY implied_move_pct DESC