IVランクとIVパーセンタイルの計算式と違い

WITH daily AS
(
    SELECT
        date,
        avg(toFloat64(implied_volatility)) * 100 AS atm_iv
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'AAPL'
      AND date >= today() - 371
      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 date
)
SELECT
    toString(toMonday(date))                    AS week,
    formatDateTime(toMonday(date), '%b %e, %Y') AS week_label,
    round(avg(atm_iv), 2)                       AS aapl_atm_iv_pct,
    round(min(atm_iv), 2)                       AS week_low_iv_pct,
    round(max(atm_iv), 2)                       AS week_high_iv_pct
FROM daily
GROUP BY toMonday(date)
ORDER BY toMonday(date)

WITH daily AS
(
    SELECT
        underlying_symbol                        AS symbol,
        date,
        avg(toFloat64(implied_volatility)) * 100 AS atm_iv
    FROM global_markets.options_greeks
    WHERE underlying_symbol IN ('AAPL', 'MSFT', 'NVDA', 'AMZN', 'TSLA', 'SPY', 'QQQ', 'KO')
      AND date >= today() - 371
      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 symbol, date
),
latest AS
(
    SELECT
        symbol,
        argMax(atm_iv, date) AS iv_now,
        max(date)            AS as_of
    FROM daily
    GROUP BY symbol
)
SELECT
    d.symbol AS symbol,
    round(100 * (l.iv_now - min(d.atm_iv)) / nullIf(max(d.atm_iv) - min(d.atm_iv), 0), 1) AS iv_rank,
    round(100 * countIf(d.atm_iv < l.iv_now) / count(), 1)                                AS iv_percentile,
    round(abs(iv_rank - iv_percentile), 1)                                                AS gap,
    formatDateTime(any(l.as_of), '%b %e, %Y')                                             AS as_of_label
FROM daily AS d
INNER JOIN latest AS l ON l.symbol = d.symbol
GROUP BY d.symbol, l.iv_now
ORDER BY gap DESC

WITH contracts AS
(
    SELECT
        date,
        days_to_expiry,
        abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) AS moneyness,
        toFloat64(implied_volatility) * 100                            AS iv_pct
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'AAPL'
      AND date >= today() - 371
      AND iv_converged = 1
      AND volume > 0
),
defs AS
(
    SELECT
        spec.1 AS iv_series,
        spec.2 AS min_dte,
        spec.3 AS max_dte,
        spec.4 AS max_moneyness
    FROM
    (
        SELECT arrayJoin([
            ('Near 30 day ATM', 20, 45, 0.05),
            ('Front month ATM', 1, 19, 0.05),
            ('Wide net, any expiry', 1, 400, 0.30)
        ]) AS spec
    )
),
daily AS
(
    SELECT
        s.iv_series AS iv_series,
        c.date      AS date,
        avg(c.iv_pct) AS atm_iv
    FROM contracts AS c
    CROSS JOIN defs AS s
    WHERE c.days_to_expiry BETWEEN s.min_dte AND s.max_dte
      AND c.moneyness < s.max_moneyness
    GROUP BY iv_series, date
),
latest AS
(
    SELECT
        iv_series,
        argMax(atm_iv, date) AS iv_now
    FROM daily
    GROUP BY iv_series
)
SELECT
    d.iv_series             AS iv_series,
    round(l.iv_now, 2)      AS current_iv_pct,
    round(min(d.atm_iv), 2) AS low_52w_iv_pct,
    round(max(d.atm_iv), 2) AS high_52w_iv_pct,
    round(100 * (l.iv_now - min(d.atm_iv)) / nullIf(max(d.atm_iv) - min(d.atm_iv), 0), 1) AS iv_rank,
    round(100 * countIf(d.atm_iv < l.iv_now) / count(), 1)                                AS iv_percentile
FROM daily AS d
INNER JOIN latest AS l ON l.iv_series = d.iv_series
GROUP BY d.iv_series, l.iv_now
ORDER BY current_iv_pct DESC

WITH daily AS
(
    SELECT
        date,
        avg(toFloat64(implied_volatility)) * 100 AS atm_iv
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'AAPL'
      AND date >= today() - 800
      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 date
),
latest AS
(
    SELECT
        argMax(atm_iv, date) AS iv_now,
        max(date)            AS as_of
    FROM daily
),
windows AS
(
    SELECT
        spec.1 AS lookback_label,
        spec.2 AS lookback_days
    FROM
    (
        SELECT arrayJoin([
            ('1 month', 30),
            ('3 months', 90),
            ('6 months', 180),
            ('12 months', 371),
            ('24 months', 742)
        ]) AS spec
    )
)
SELECT
    w.lookback_label AS lookback_label,
    round(100 * (l.iv_now - min(d.atm_iv)) / nullIf(max(d.atm_iv) - min(d.atm_iv), 0), 1) AS iv_rank,
    round(100 * countIf(d.atm_iv < l.iv_now) / count(), 1)                                AS iv_percentile,
    count()                                                                               AS observations
FROM daily AS d
CROSS JOIN windows AS w
CROSS JOIN latest AS l
WHERE d.date >= l.as_of - w.lookback_days
GROUP BY w.lookback_label, w.lookback_days, l.iv_now
ORDER BY w.lookback_days