0DTEオプションは高リスク？ガンマとセータで検証

WITH atm_calls AS
(
    SELECT
        days_to_expiry              AS dte,
        toFloat64(option_close)     AS premium,
        toFloat64(underlying_close) AS spot,
        toFloat64(delta)            AS dlt
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND lower(toString(option_type)) IN ('call', 'c')
      AND date >= toDate('2026-06-01')
      AND date <  toDate('2026-07-01')
      AND days_to_expiry > 0
      AND days_to_expiry <= 45
      AND iv_converged = 1
      AND volume > 0
      AND option_close > 0
      AND toFloat64(delta) BETWEEN 0.2 AND 0.8
      AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.0025
)
SELECT
    multiIf(dte <= 1,  '1 day (next session)',
            dte <= 5,  '2-5 days',
            dte <= 10, '6-10 days',
            dte <= 20, '11-20 days',
                       '21-45 days')                   AS dte_bucket,
    round(avg(premium), 2)                             AS avg_premium,
    round(avg(premium / spot) * 100, 2)                AS breakeven_move_pct,
    round(avg(premium / spot) * 200, 2)                AS double_move_pct,
    round(avg(premium / (dlt * spot)) * 100, 2)        AS delta_move_pct
FROM atm_calls
GROUP BY dte_bucket
ORDER BY min(dte)

WITH atm_calls AS
(
    SELECT
        days_to_expiry          AS dte,
        toFloat64(option_close) AS premium,
        toFloat64(gamma)        AS gma,
        toFloat64(theta)        AS tht
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND lower(toString(option_type)) IN ('call', 'c')
      AND date >= toDate('2026-06-01')
      AND date <  toDate('2026-07-01')
      AND days_to_expiry > 0
      AND days_to_expiry <= 45
      AND iv_converged = 1
      AND volume > 0
      AND option_close > 0
      AND toFloat64(delta) BETWEEN 0.2 AND 0.8
      AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.0025
),
ladder AS
(
    SELECT
        multiIf(dte <= 1,  '1 day (next session)',
                dte <= 5,  '2-5 days',
                dte <= 10, '6-10 days',
                dte <= 20, '11-20 days',
                           '21-45 days')       AS dte_bucket,
        min(dte)                               AS dte_lo,
        avg(gma / premium)                     AS gamma_raw,
        avg(abs(tht) / premium) * 100          AS theta_raw
    FROM atm_calls
    GROUP BY dte_bucket
)
SELECT
    dte_bucket,
    round(gamma_raw, 4)                                                                   AS gamma_per_premium_dollar,
    round(theta_raw, 1)                                                                   AS theta_pct_per_day,
    round(gamma_raw / (SELECT gamma_raw FROM ladder WHERE dte_bucket = '21-45 days'), 1)  AS gamma_ratio_vs_month_out,
    round(theta_raw / (SELECT theta_raw FROM ladder WHERE dte_bucket = '21-45 days'), 1)  AS theta_ratio_vs_month_out
FROM ladder
ORDER BY dte_lo

SELECT
    concat(if(half_pct > 0, '+', ''), toString(half_pct / 2), '%')  AS moneyness,
    round(avgIf(gma * spot / 100, dte <= 1), 3)                      AS next_day_delta_shift,
    round(avgIf(gma * spot / 100, dte BETWEEN 21 AND 45), 3)         AS month_out_delta_shift
FROM
(
    SELECT
        toInt32(round((toFloat64(strike_price) / toFloat64(underlying_close) - 1) * 200)) AS half_pct,
        days_to_expiry                                                                     AS dte,
        toFloat64(gamma)                                                                   AS gma,
        toFloat64(underlying_close)                                                        AS spot
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND date >= toDate('2026-06-01')
      AND date <  toDate('2026-07-01')
      AND ((days_to_expiry > 0 AND days_to_expiry <= 1) OR days_to_expiry BETWEEN 21 AND 45)
      AND iv_converged = 1
      AND volume > 0
      AND option_close > 0
      AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.0225
      AND ((lower(toString(option_type)) IN ('call', 'c') AND strike_price >= underlying_close)
        OR (lower(toString(option_type)) IN ('put', 'p')  AND strike_price <  underlying_close))
)
GROUP BY half_pct
HAVING countIf(dte <= 1) > 0 AND countIf(dte BETWEEN 21 AND 45) > 0
ORDER BY half_pct

WITH spy_by_day AS
(
    SELECT
        toDate(date)                             AS d,
        medianExact(toFloat64(underlying_close)) AS spot
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND date >= toDate('2026-06-01')
      AND date <  toDate('2026-07-01')
      AND underlying_close > 0
    GROUP BY d
),
last_sessions AS
(
    SELECT
        toDate(expiration_date) AS exp_date,
        max(toDate(date))       AS prior_session
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND expiration_date >= toDate('2026-06-01')
      AND expiration_date <  toDate('2026-07-01')
      AND date >= toDate('2026-05-22')
      AND date <  expiration_date
      AND volume > 0
    GROUP BY exp_date
),
atm AS
(
    SELECT
        toDate(g.expiration_date)                                                                                AS exp_date,
        argMin(toFloat64(g.strike_price), abs(toFloat64(g.strike_price) - toFloat64(g.underlying_close)))        AS strike,
        argMin(toFloat64(g.option_close), abs(toFloat64(g.strike_price) - toFloat64(g.underlying_close)))        AS premium_before,
        argMin(toFloat64(g.underlying_close), abs(toFloat64(g.strike_price) - toFloat64(g.underlying_close)))    AS spy_before
    FROM global_markets.options_greeks AS g
    INNER JOIN last_sessions AS ls
        ON ls.exp_date = toDate(g.expiration_date) AND ls.prior_session = toDate(g.date)
    WHERE g.underlying_symbol = 'SPY'
      AND lower(toString(g.option_type)) IN ('call', 'c')
      AND g.date >= toDate('2026-05-22')
      AND g.date <  toDate('2026-07-01')
      AND g.volume > 0
      AND g.option_close > 0
    GROUP BY exp_date
)
SELECT
    toString(a.exp_date)                                                                   AS expiry_date,
    concat(formatDateTime(a.exp_date, '%a %b '), toString(toDayOfMonth(a.exp_date)))       AS expiry_label,
    round(a.premium_before, 2)                                                             AS premium_prior_close,
    round(greatest(s.spot - a.strike, 0.0), 2)                                             AS value_at_expiry,
    round(greatest(s.spot - a.strike, 0.0) / a.premium_before * 100, 0)                    AS pct_of_premium_left,
    round((s.spot / a.spy_before - 1) * 100, 2)                                            AS spy_move_pct
FROM atm AS a
INNER JOIN spy_by_day AS s
    ON s.d = a.exp_date
ORDER BY a.exp_date

WITH spy_by_day AS
(
    SELECT
        toDate(date)                             AS d,
        medianExact(toFloat64(underlying_close)) AS spot
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND date >= toDate('2026-06-01')
      AND date <  toDate('2026-07-01')
      AND underlying_close > 0
    GROUP BY d
),
last_sessions AS
(
    SELECT
        toDate(expiration_date) AS exp_date,
        max(toDate(date))       AS prior_session
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND expiration_date >= toDate('2026-06-01')
      AND expiration_date <  toDate('2026-07-01')
      AND date >= toDate('2026-05-22')
      AND date <  expiration_date
      AND volume > 0
    GROUP BY exp_date
),
atm AS
(
    SELECT
        toDate(g.expiration_date)                                                                                AS exp_date,
        argMin(toFloat64(g.strike_price), abs(toFloat64(g.strike_price) - toFloat64(g.underlying_close)))        AS strike,
        argMin(toFloat64(g.option_close), abs(toFloat64(g.strike_price) - toFloat64(g.underlying_close)))        AS premium_before
    FROM global_markets.options_greeks AS g
    INNER JOIN last_sessions AS ls
        ON ls.exp_date = toDate(g.expiration_date) AND ls.prior_session = toDate(g.date)
    WHERE g.underlying_symbol = 'SPY'
      AND lower(toString(g.option_type)) IN ('call', 'c')
      AND g.date >= toDate('2026-05-22')
      AND g.date <  toDate('2026-07-01')
      AND g.volume > 0
      AND g.option_close > 0
    GROUP BY exp_date
),
outcomes AS
(
    SELECT
        a.exp_date                                          AS exp_date,
        greatest(s.spot - a.strike, 0.0) / a.premium_before AS premium_ratio
    FROM atm AS a
    INNER JOIN spy_by_day AS s
        ON s.d = a.exp_date
)
SELECT
    tupleElement(b, 1)                                                                         AS outcome,
    countIf(o.premium_ratio >= tupleElement(b, 2) AND o.premium_ratio < tupleElement(b, 3))    AS expiries
FROM
(
    SELECT arrayJoin([
        ('1. Finished at or near zero (5% of the premium or less)', -1.0, 0.05),
        ('2. Lost more than half',                                   0.05, 0.5),
        ('3. Lost up to half',                                       0.5,  1.0),
        ('4. Gained, less than doubled',                             1.0,  2.0),
        ('5. Doubled or better',                                     2.0,  1000000.0)
    ]) AS b
) AS buckets
CROSS JOIN outcomes AS o
GROUP BY outcome
ORDER BY outcome