バンナとチャームとは:IVと時間で動くデルタ

WITH daily AS
(
    SELECT
        ticker,
        expiration_date,
        strike_price,
        date,
        toFloat64(delta)                                          AS delta,
        multiIf(toFloat64(implied_volatility) > 1.5,
                toFloat64(implied_volatility),
                toFloat64(implied_volatility) * 100)              AS iv_pt,
        toFloat64(underlying_close)                               AS spot,
        toFloat64(strike_price) / toFloat64(underlying_close) - 1 AS moneyness
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND lower(toString(option_type)) LIKE 'c%'
      AND date >= '2025-09-01'
      AND date <  '2026-09-01'
      AND iv_converged = 1
      AND volume > 0
      AND days_to_expiry BETWEEN 20 AND 45
),
steps AS
(
    SELECT
        ticker,
        date,
        moneyness,
        delta - lagInFrame(delta) OVER (PARTITION BY ticker, expiration_date, strike_price ORDER BY date)     AS delta_change,
        iv_pt - lagInFrame(iv_pt) OVER (PARTITION BY ticker, expiration_date, strike_price ORDER BY date)     AS iv_change_pt,
        spot  / lagInFrame(spot)  OVER (PARTITION BY ticker, expiration_date, strike_price ORDER BY date) - 1 AS spot_change,
        dateDiff('day', lagInFrame(date) OVER (PARTITION BY ticker, expiration_date, strike_price ORDER BY date), date) AS gap_days
    FROM daily
)
SELECT
    multiIf(moneyness < -0.05,  'ITM 5-12%',
            moneyness < -0.02,  'ITM 2-5%',
            moneyness < -0.005, 'ITM 0.5-2%',
            moneyness <  0.005, 'ATM 0.5%以内',
            moneyness <  0.02,  'OTM 0.5-2%',
            moneyness <  0.05,  'OTM 2-5%',
                                'OTM 5-12%')                       AS moneyness_band,
    round(quantileDeterministic(0.5)(delta_change / iv_change_pt,
                                     cityHash64(ticker, date)), 4)  AS delta_shift_per_vol_point,
    count()                                                        AS pair_count
FROM steps
WHERE gap_days = 1
  AND abs(spot_change) < 0.002
  AND abs(iv_change_pt) >= 0.4
  AND abs(moneyness) <= 0.12
GROUP BY moneyness_band
HAVING count() >= 10
ORDER BY min(moneyness)

WITH daily AS
(
    SELECT
        ticker,
        expiration_date,
        strike_price,
        date,
        days_to_expiry,
        toFloat64(delta)                                          AS delta,
        multiIf(toFloat64(implied_volatility) > 1.5,
                toFloat64(implied_volatility),
                toFloat64(implied_volatility) * 100)              AS iv_pt,
        toFloat64(underlying_close)                               AS spot,
        toFloat64(strike_price) / toFloat64(underlying_close) - 1 AS moneyness
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND lower(toString(option_type)) LIKE 'c%'
      AND date >= '2025-09-01'
      AND date <  '2026-09-01'
      AND iv_converged = 1
      AND volume > 0
      AND days_to_expiry <= 90
),
steps AS
(
    SELECT
        ticker,
        date,
        moneyness,
        days_to_expiry,
        delta - lagInFrame(delta) OVER (PARTITION BY ticker, expiration_date, strike_price ORDER BY date)        AS delta_change,
        abs(iv_pt - lagInFrame(iv_pt) OVER (PARTITION BY ticker, expiration_date, strike_price ORDER BY date))   AS iv_move_pt,
        abs(spot / lagInFrame(spot) OVER (PARTITION BY ticker, expiration_date, strike_price ORDER BY date) - 1) AS spot_move,
        dateDiff('day', lagInFrame(date) OVER (PARTITION BY ticker, expiration_date, strike_price ORDER BY date), date) AS gap_days
    FROM daily
)
SELECT
    multiIf(days_to_expiry <=  2, '残り0-2日',
            days_to_expiry <=  5, '残り3-5日',
            days_to_expiry <= 10, '残り6-10日',
            days_to_expiry <= 20, '残り11-20日',
            days_to_expiry <= 45, '残り21-45日',
                                  '残り46-90日') AS dte_band,
    round(quantileDeterministicIf(0.5)(delta_change, cityHash64(ticker, date), moneyness < -0.005), 4) AS itm_delta_drift,
    round(quantileDeterministicIf(0.5)(delta_change, cityHash64(ticker, date), moneyness >  0.005), 4) AS otm_delta_drift,
    count()                                                                                           AS pair_count
FROM steps
WHERE gap_days = 1
  AND spot_move < 0.002
  AND iv_move_pt < 0.5
  AND abs(moneyness) BETWEEN 0.005 AND 0.03
GROUP BY dte_band
HAVING countIf(moneyness < -0.005) >= 10
   AND countIf(moneyness >  0.005) >= 10
ORDER BY min(days_to_expiry)

WITH
(
    SELECT argMax(toFloat64(underlying_close), date)
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND date >= '2026-06-15'
      AND date <= '2026-06-19'
) AS expiry_spot
SELECT
    toString(date)                                                                  AS session_date,
    concat(toString(toMonth(date)), '月', toString(toDayOfMonth(date)), '日')        AS session_label,
    round(avgIf(toFloat64(delta), toFloat64(strike_price) < expiry_spot * 0.99), 3)  AS below_spot_call_delta,
    round(avgIf(toFloat64(delta), toFloat64(strike_price) > expiry_spot * 1.01), 3)  AS above_spot_call_delta,
    round(avgIf(toFloat64(delta), toFloat64(strike_price) < expiry_spot * 0.99)
        - avgIf(toFloat64(delta), toFloat64(strike_price) > expiry_spot * 1.01), 3)  AS delta_gap
FROM global_markets.options_greeks
WHERE underlying_symbol = 'SPY'
  AND lower(toString(option_type)) LIKE 'c%'
  AND expiration_date = '2026-06-18'
  AND date >= '2026-06-01'
  AND date <= '2026-06-19'
  AND iv_converged = 1
  AND volume > 0
  AND abs(toFloat64(strike_price) / expiry_spot - 1) < 0.04
GROUP BY date
HAVING countIf(toFloat64(strike_price) < expiry_spot * 0.99) >= 3
   AND countIf(toFloat64(strike_price) > expiry_spot * 1.01) >= 3
ORDER BY date

WITH atm AS
(
    SELECT
        date,
        max(toFloat64(underlying_close))                   AS spot,
        round(avg(toFloat64(implied_volatility)) * 100, 3) AS atm_iv_pct
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND date >= '2025-09-01'
      AND date <  '2026-09-01'
      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.02
    GROUP BY date
),
steps AS
(
    SELECT
        date,
        100 * (spot / lagInFrame(spot) OVER (ORDER BY date) - 1)     AS spot_move_pct,
        atm_iv_pct - lagInFrame(atm_iv_pct) OVER (ORDER BY date)     AS iv_move_pt,
        dateDiff('day', lagInFrame(date) OVER (ORDER BY date), date) AS gap_days
    FROM atm
)
SELECT
    formatDateTime(toStartOfMonth(date), '%Y-%m')                            AS month,
    concat(toString(toYear(date)), '年', toString(toMonth(date)), '月')       AS month_label,
    countIf(abs(spot_move_pct) < 0.25)                                       AS quiet_spot_sessions,
    countIf(abs(spot_move_pct) < 0.25 AND abs(iv_move_pt) >= 1)              AS quiet_spot_iv_shift_sessions,
    round(100 * countIf(abs(spot_move_pct) < 0.25 AND abs(iv_move_pt) >= 1)
              / countIf(abs(spot_move_pct) < 0.25), 1)                       AS iv_shift_share_pct
FROM steps
WHERE gap_days BETWEEN 1 AND 5
GROUP BY month, month_label
HAVING countIf(abs(spot_move_pct) < 0.25) > 0
ORDER BY month