2,170 answered market questions

WITH iv AS
(
    SELECT
        toStartOfMonth(date)          AS month,
        avg(implied_volatility) * 100 AS iv_pct
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND date >= toStartOfMonth(today() - 400)
      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 month
),
daily AS
(
    SELECT
        date,
        close_f / prev_close - 1 AS ret
    FROM
    (
        SELECT
            date,
            close_f,
            lagInFrame(close_f) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
        FROM
        (
            SELECT date, avg(toFloat64(close)) AS close_f
            FROM global_markets.stocks_daily_aggs
            WHERE ticker = 'SPY'
              AND date >= toStartOfMonth(today() - 400) - 10
            GROUP BY date
        )
    )
    WHERE prev_close > 0
),
realized AS
(
    SELECT
        toStartOfMonth(date)  AS month,
        stddevSamp(ret) * 100 AS stdev_pct,
        avg(abs(ret)) * 100   AS avg_abs_pct
    FROM daily
    GROUP BY month
    HAVING count() >= 15
)
SELECT
    toString(i.month)                AS month,
    formatDateTime(i.month, '%b %Y') AS month_label,
    round(i.iv_pct / 16, 2)          AS implied_daily_move_pct,
    round(r.stdev_pct, 2)            AS realized_stdev_pct,
    round(r.avg_abs_pct, 2)          AS realized_avg_move_pct
FROM iv AS i
INNER JOIN realized AS r ON i.month = r.month
ORDER BY i.month