iv30_trace

SELECT
    toString(date)                                              AS session_date,
    concat(monthName(date), ' ', toString(toDayOfMonth(date)), ', ', toString(toYear(date))) AS as_of_label,
    round(near_iv * 100, 2)                                     AS near_iv_pct,
    round(far_iv * 100, 2)                                      AS far_iv_pct,
    round(sqrt((near_iv * near_iv * near_dte * (far_dte - 30)
              + far_iv * far_iv * far_dte * (30 - near_dte))
              / (far_dte - near_dte) / 30) * 100, 2)            AS iv30_pct
FROM
(
    SELECT
        date,
        maxIf(days_to_expiry, days_to_expiry <= 30)             AS near_dte,
        minIf(days_to_expiry, days_to_expiry > 30)              AS far_dte,
        argMaxIf(atm_iv, days_to_expiry, days_to_expiry <= 30)  AS near_iv,
        argMinIf(atm_iv, days_to_expiry, days_to_expiry > 30)   AS far_iv
    FROM
    (
        SELECT
            date,
            days_to_expiry,
            avg(toFloat64(implied_volatility))  AS atm_iv,
            count()                             AS contracts
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'AAPL'
          AND date BETWEEN '2026-06-15' AND '2026-08-31'
          AND iv_converged = 1
          AND volume > 0
          AND days_to_expiry BETWEEN 7 AND 90
          AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.025
        GROUP BY date, days_to_expiry
        HAVING contracts >= 2
    )
    GROUP BY date
    HAVING countIf(days_to_expiry <= 30) > 0
       AND countIf(days_to_expiry > 30) > 0
)
ORDER BY date