2,170 answered market questions

WITH
    daily AS
    (
        SELECT
            date             AS session_date,
            toFloat64(close) AS close_px,
            lagInFrame(toFloat64(close)) OVER
                (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= today() - 560
          AND date <  today()
    ),
    realized AS
    (
        SELECT
            toStartOfMonth(session_date)                                       AS month_start,
            round(100 * sqrt(252) * stddevSamp(log(close_px / prev_close)), 1) AS realized_vol_pct
        FROM daily
        WHERE prev_close > 0
        GROUP BY month_start
        HAVING count() >= 15
    ),
    implied AS
    (
        SELECT
            toStartOfMonth(date)                  AS month_start,
            round(100 * avg(implied_volatility), 1) AS implied_vol_pct
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'SPY'
          AND date >= today() - 560
          AND date <  today()
          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_start
        HAVING count() >= 100
    )
SELECT
    formatDateTime(imp.month_start, '%Y-%m')                    AS month,
    imp.implied_vol_pct                                         AS implied_vol_pct,
    rea.realized_vol_pct                                        AS realized_next_month_pct,
    round(imp.implied_vol_pct - rea.realized_vol_pct, 1)        AS gap_pct
FROM implied AS imp
INNER JOIN realized AS rea ON rea.month_start = addMonths(imp.month_start, 1)
ORDER BY month