オプションの大半は無価値で満期を迎えるのか

WITH final_print AS
(
    SELECT
        underlying_symbol                         AS symbol,
        ticker,
        argMax(option_type, date)                 AS contract_type,
        toFloat64(argMax(strike_price, date))     AS strike,
        toFloat64(argMax(underlying_close, date)) AS spot_last,
        min(days_to_expiry)                       AS final_dte
    FROM global_markets.options_greeks
    WHERE underlying_symbol IN ('SPY', 'AAPL', 'MSFT', 'NVDA', 'AMZN', 'KO')
      AND (lower(toString(option_type)) LIKE 'c%' OR lower(toString(option_type)) LIKE 'p%')
      AND volume > 0
      AND expiration_date >= '2025-08-01'
      AND expiration_date <  '2026-08-01'
      AND date >= '2025-06-01'
      AND date <  '2026-08-01'
    GROUP BY symbol, ticker
    HAVING final_dte <= 3
)
SELECT
    symbol,
    round(100 * countIf(
        (lower(toString(contract_type)) LIKE 'c%' AND spot_last <= strike)
     OR (lower(toString(contract_type)) LIKE 'p%' AND spot_last >= strike)
    ) / count(), 1)   AS pct_expired_otm,
    round(100 * countIf(
        (lower(toString(contract_type)) LIKE 'c%' AND spot_last >  strike)
     OR (lower(toString(contract_type)) LIKE 'p%' AND spot_last <  strike)
    ) / count(), 1)   AS pct_expired_itm,
    count()           AS expiring_contracts
FROM final_print
GROUP BY symbol
ORDER BY pct_expired_otm DESC

WITH
(
    SELECT sum(volume)
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND volume > 0
      AND date >= '2026-04-01'
      AND date <  '2026-07-01'
) AS quarter_volume
SELECT
    multiIf(days_to_expiry = 0,   '0 (expires that day)',
            days_to_expiry <= 5,  '1 to 5 days',
            days_to_expiry <= 21, '6 to 21 days',
            days_to_expiry <= 60, '22 to 60 days',
                                  '61 days or more')  AS days_to_expiry_bucket,
    round(100 * sum(volume) / quarter_volume, 1)      AS share_of_volume_pct,
    countDistinct(ticker)                             AS contracts_with_volume
FROM global_markets.options_greeks
WHERE underlying_symbol = 'SPY'
  AND volume > 0
  AND date >= '2026-04-01'
  AND date <  '2026-07-01'
GROUP BY days_to_expiry_bucket
ORDER BY min(days_to_expiry)

WITH entry AS
(
    SELECT
        ticker,
        argMin(option_type, date)                 AS contract_type,
        toFloat64(argMin(strike_price, date))     AS strike,
        toFloat64(argMin(underlying_close, date)) AS spot_30d_out
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND (lower(toString(option_type)) LIKE 'c%' OR lower(toString(option_type)) LIKE 'p%')
      AND days_to_expiry BETWEEN 28 AND 32
      AND volume > 0
      AND date >= '2025-07-01'
      AND date <  '2026-07-01'
    GROUP BY ticker
),
last_print AS
(
    SELECT
        ticker,
        toFloat64(argMax(underlying_close, date)) AS spot_at_last_print,
        min(days_to_expiry)                       AS final_dte
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND volume > 0
      AND date >= '2025-07-01'
      AND date <  '2026-08-01'
    GROUP BY ticker
    HAVING final_dte <= 3
)
SELECT
    multiIf(otm_pct < 2,  'under 2% out',
            otm_pct < 5,  '2% to 5% out',
            otm_pct < 10, '5% to 10% out',
            otm_pct < 20, '10% to 20% out',
                          '20% or more out')            AS distance_at_30_days,
    round(100 * countIf(finished_itm) / count(), 1)      AS pct_finished_itm,
    count()                                             AS contracts
FROM
(
    SELECT
        if(lower(toString(e.contract_type)) LIKE 'c%',
           100 * (e.strike / e.spot_30d_out - 1),
           100 * (1 - e.strike / e.spot_30d_out)) AS otm_pct,
        if(lower(toString(e.contract_type)) LIKE 'c%',
           s.spot_at_last_print > e.strike,
           s.spot_at_last_print < e.strike)       AS finished_itm
    FROM entry AS e
    INNER JOIN last_print AS s ON s.ticker = e.ticker
)
WHERE otm_pct > 0
GROUP BY distance_at_30_days
ORDER BY min(otm_pct)

SELECT
    toString(date)               AS date,
    round(sum(volume) / 1000, 1) AS volume_thousands,
    countDistinct(ticker)        AS contracts_with_volume
FROM global_markets.options_greeks
WHERE underlying_symbol = 'SPY'
  AND expiration_date = '2026-05-15'
  AND volume > 0
  AND days_to_expiry <= 30
  AND date >= '2026-04-01'
  AND date <  '2026-05-16'
GROUP BY date
ORDER BY date