DTEとは？オプション取引における満期までの残存日数の意味

SELECT argMax(contract, (vol, contract)) AS option_ticker,
       argMax(traded_on, (vol, contract)) AS traded_on,
       argMax(expires_on, (vol, contract)) AS expires_on,
       argMax(dte, (vol, contract)) AS calendar_dte,
       multiIf(max(vol) >= 1000000,
               concat(toString(intDiv(toUInt64(max(vol)), 1000000)), ',',
                      leftPad(toString(intDiv(toUInt64(max(vol)), 1000) % 1000), 3, '0'), ',',
                      leftPad(toString(toUInt64(max(vol)) % 1000), 3, '0')),
               max(vol) >= 1000,
               concat(toString(intDiv(toUInt64(max(vol)), 1000)), ',',
                      leftPad(toString(toUInt64(max(vol)) % 1000), 3, '0')),
               toString(toUInt64(max(vol)))) AS contracts_traded
FROM (
    SELECT ticker AS contract,
           replaceRegexpAll(formatDateTime(toDate(toTimeZone(window_start, 'America/New_York')), '%M %e, %Y'), '  ', ' ') AS traded_on,
           replaceRegexpAll(formatDateTime(toDateOrNull(concat('20', substring(ticker, length(ticker) - 14, 6))), '%M %e, %Y'), '  ', ' ') AS expires_on,
           dateDiff('day',
                    toDate(toTimeZone(window_start, 'America/New_York')),
                    toDateOrNull(concat('20', substring(ticker, length(ticker) - 14, 6)))) AS dte,
           multiIf(dte = 0, '0 DTE', dte = 1, '1 DTE', dte <= 7, '2-7 DTE',
                   dte <= 30, '8-30 DTE', dte <= 90, '31-90 DTE', '91+ DTE') AS bucket,
           sum(volume) AS vol
    FROM global_markets.options_minute_aggs
    WHERE ticker LIKE 'O:SPY2%'
      AND window_start >= '2026-07-08 04:00:00'
      AND window_start < '2026-07-09 04:00:00'
    GROUP BY contract, traded_on, expires_on, dte, bucket
    HAVING dte >= 0
)
GROUP BY bucket
ORDER BY min(dte)

SELECT multiIf(dte = 0, '0 DTE (expires today)',
               dte = 1, '1 DTE',
               dte <= 7, '2-7 DTE',
               dte <= 30, '8-30 DTE',
               dte <= 90, '31-90 DTE',
               '91+ DTE') AS bucket,
       round(sum(vol) / 1e6, 2) AS contracts_mm,
       round(100.0 * sum(vol) / sum(sum(vol)) OVER (), 1) AS pct_of_volume
FROM (
    SELECT toFloat64(volume) AS vol,
           dateDiff('day',
                    toDate(toTimeZone(window_start, 'America/New_York')),
                    toDateOrNull(concat('20', substring(ticker, length(ticker) - 14, 6)))) AS dte
    FROM global_markets.options_minute_aggs
    WHERE window_start >= '2026-07-08 04:00:00'
      AND window_start < '2026-07-09 04:00:00'
)
WHERE dte >= 0
GROUP BY bucket
ORDER BY min(dte)

SELECT round(100.0 * sumIf(vol, dte <= 7) / sum(vol), 1) AS pct_week_or_less,
       round(100.0 * sumIf(vol, dte >= 31) / sum(vol), 1) AS pct_31_dte_plus,
       round(sum(vol) / 1e6, 1) AS total_contracts_mm
FROM (
    SELECT toFloat64(volume) AS vol,
           dateDiff('day',
                    toDate(toTimeZone(window_start, 'America/New_York')),
                    toDateOrNull(concat('20', substring(ticker, length(ticker) - 14, 6)))) AS dte
    FROM global_markets.options_minute_aggs
    WHERE window_start >= '2026-07-08 04:00:00'
      AND window_start < '2026-07-09 04:00:00'
)
WHERE dte >= 0

WITH spot AS (
    SELECT avg(close) AS px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2026-07-08 11:30:00', 'America/New_York')
      AND window_start < toDateTime('2026-07-08 13:00:00', 'America/New_York')
),
chain AS (
    SELECT toDateOrNull(concat('20', substring(ticker, length(ticker) - 14, 6))) AS expiry,
           toFloat64(substring(ticker, length(ticker) - 7, 8)) / 1000 AS strike,
           avg(close) AS opt_px
    FROM global_markets.options_minute_aggs
    WHERE ticker LIKE 'O:SPY2%'
      AND substring(ticker, length(ticker) - 8, 1) = 'C'
      AND window_start >= toDateTime('2026-07-08 11:30:00', 'America/New_York')
      AND window_start < toDateTime('2026-07-08 13:00:00', 'America/New_York')
    GROUP BY expiry, strike
    HAVING sum(volume) > 0
       AND abs(strike - (SELECT px FROM spot)) <= 3
       AND dateDiff('day', toDate('2026-07-08'), expiry) >= 0
),
atm AS (
    SELECT dateDiff('day', toDate('2026-07-08'), expiry) AS dte,
           argMin(opt_px, (abs(strike - (SELECT px FROM spot)), strike)) AS call_price
    FROM chain
    GROUP BY expiry
),
targets AS (
    SELECT arrayJoin([0, 1, 2, 7, 14, 30, 60, 90, 180, 365, 730]) AS target
),
nearest AS (
    SELECT argMin(dte, (abs(dte - target), dte)) AS pick_dte,
           argMin(call_price, (abs(dte - target), dte)) AS pick_px
    FROM targets CROSS JOIN atm
    GROUP BY target
)
SELECT concat(toString(pick_dte), ' DTE') AS days_to_expiration,
       round(pick_px, 2) AS atm_call_price,
       round(pick_px / greatest(pick_dte, 1), 2) AS premium_per_remaining_day
FROM nearest
GROUP BY days_to_expiration, atm_call_price, premium_per_remaining_day, pick_dte
ORDER BY pick_dte

SELECT underlying,
       uniqExact(expiry) AS expiries_traded,
       min(dte) AS shortest_dte,
       max(dte) AS longest_dte,
       round(100.0 * sumIf(vol, dte = 0) / sum(vol), 1) AS pct_0dte,
       round(sum(vol) / 1e6, 2) AS contracts_mm
FROM (
    SELECT splitByChar(':', ticker)[2] AS raw,
           substring(raw, 1, length(raw) - 15) AS underlying,
           toFloat64(volume) AS vol,
           toDateOrNull(concat('20', substring(ticker, length(ticker) - 14, 6))) AS expiry,
           dateDiff('day', toDate(toTimeZone(window_start, 'America/New_York')), expiry) AS dte
    FROM global_markets.options_minute_aggs
    WHERE (ticker LIKE 'O:SPY2%' OR ticker LIKE 'O:QQQ2%' OR ticker LIKE 'O:IWM2%'
           OR ticker LIKE 'O:AAPL2%' OR ticker LIKE 'O:TSLA2%' OR ticker LIKE 'O:KO2%')
      AND window_start >= '2026-07-08 04:00:00'
      AND window_start < '2026-07-09 04:00:00'
)
WHERE dte >= 0
GROUP BY underlying
ORDER BY contracts_mm DESC

SELECT year,
       round(100.0 * sumIf(vol, dte = 0) / sum(vol), 1) AS pct_0dte,
       round(100.0 * sumIf(vol, dte <= 7) / sum(vol), 1) AS pct_week_or_less,
       round(sum(vol) / 1e6, 1) AS spy_contracts_mm
FROM (
    SELECT toYear(toTimeZone(window_start, 'America/New_York')) AS year,
           toFloat64(volume) AS vol,
           dateDiff('day',
                    toDate(toTimeZone(window_start, 'America/New_York')),
                    toDateOrNull(concat('20', substring(ticker, length(ticker) - 14, 6)))) AS dte
    FROM global_markets.options_minute_aggs
    WHERE ticker LIKE 'O:SPY2%'
      AND toMonth(toTimeZone(window_start, 'America/New_York')) = 6
      AND toDayOfMonth(toTimeZone(window_start, 'America/New_York')) BETWEEN 8 AND 12
      AND toYear(toTimeZone(window_start, 'America/New_York')) >= 2020
)
WHERE dte >= 0
GROUP BY year
ORDER BY year