トリプルウィッチングとは？取引高と変動性

WITH daily AS (
    SELECT date AS day,
           sum(toFloat64(volume)) / 1e9 AS shares_b
    FROM global_markets.stocks_daily_aggs
    WHERE date >= '2024-09-01'
      AND date <  '2026-07-01'
      AND toMonth(date) IN (3, 6, 9, 12)
    GROUP BY day
),
witching AS (
    SELECT toStartOfMonth(day) AS m,
           maxIf(day, day <= addDays(toStartOfMonth(day),
                 ((5 - toDayOfWeek(toStartOfMonth(day)) + 7) % 7) + 14)) AS witching_day
    FROM daily
    GROUP BY m
)
SELECT toString(w.witching_day) AS witching_session,
       round(anyIf(d.shares_b, d.day = w.witching_day), 1) AS witching_shares_b,
       round(quantileDeterministicIf(0.5)(d.shares_b, cityHash64(toString(d.day)), d.day != w.witching_day), 1) AS other_days_median_b,
       round(anyIf(d.shares_b, d.day = w.witching_day)
             / quantileDeterministicIf(0.5)(d.shares_b, cityHash64(toString(d.day)), d.day != w.witching_day), 2) AS times_median,
       round(anyIf(d.shares_b, d.day = w.witching_day) - maxIf(d.shares_b, d.day != w.witching_day), 2) AS gap_to_next_heaviest
FROM daily AS d
INNER JOIN witching AS w ON toStartOfMonth(d.day) = w.m
GROUP BY w.witching_day
HAVING countIf(d.day != w.witching_day) > 0
ORDER BY w.witching_day

SELECT toString(expiry) AS expiry,
       round(sum(vol) / 1e6, 1) AS contracts_mm
FROM (
    SELECT toDateOrNull(concat('20', substring(ticker, length(ticker) - 14, 6))) AS expiry,
           toFloat64(volume) AS vol
    FROM global_markets.options_minute_aggs
    WHERE window_start >= '2026-06-01 04:00:00'
      AND window_start < '2026-06-19 04:00:00'
)
WHERE expiry IS NOT NULL
GROUP BY expiry
ORDER BY contracts_mm DESC
LIMIT 8

SELECT toString(d) AS session_date,
       round(sum(vol) / 1e6, 1) AS all_contracts_mm,
       round(sumIf(vol, expiry = d) / 1e6, 1) AS expiring_same_day_mm,
       round(sumIf(vol, expiry = d) / sum(vol) * 100, 1) AS pct_expiring_same_day
FROM (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           toDateOrNull(concat('20', substring(ticker, length(ticker) - 14, 6))) AS expiry,
           toFloat64(volume) AS vol
    FROM global_markets.options_minute_aggs
    WHERE (window_start >= '2026-06-05 04:00:00' AND window_start < '2026-06-06 04:00:00')
       OR (window_start >= '2026-06-12 04:00:00' AND window_start < '2026-06-13 04:00:00')
       OR (window_start >= '2026-06-18 04:00:00' AND window_start < '2026-06-19 04:00:00')
)
WHERE expiry IS NOT NULL
GROUP BY d
ORDER BY d

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS day,
           toFloat64(max(high) - min(low)) / toFloat64(argMin(open, window_start)) * 100 AS range_pct,
           abs(toFloat64(argMax(close, window_start)) - toFloat64(argMin(open, window_start)))
               / toFloat64(argMin(open, window_start)) * 100 AS net_move_pct
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= '2024-09-01 04:00:00'
      AND window_start < '2026-07-01 04:00:00'
      AND toMonth(toTimeZone(window_start, 'America/New_York')) IN (3, 6, 9, 12)
      AND toHour(toTimeZone(window_start, 'America/New_York')) >= 9
      AND toHour(toTimeZone(window_start, 'America/New_York')) < 16
      AND NOT (toHour(toTimeZone(window_start, 'America/New_York')) = 9
               AND toMinute(toTimeZone(window_start, 'America/New_York')) < 30)
    GROUP BY day
),
witching AS (
    SELECT toStartOfMonth(day) AS m,
           maxIf(day, day <= addDays(toStartOfMonth(day),
                 ((5 - toDayOfWeek(toStartOfMonth(day)) + 7) % 7) + 14)) AS witching_day
    FROM daily
    GROUP BY m
)
SELECT toString(w.witching_day) AS witching_session,
       round(anyIf(d.range_pct, d.day = w.witching_day), 2) AS witching_range_pct,
       round(quantileDeterministicIf(0.5)(d.range_pct, cityHash64(toString(d.day)), d.day != w.witching_day), 2) AS other_days_median_range_pct,
       round(anyIf(d.net_move_pct, d.day = w.witching_day), 2) AS witching_net_move_pct,
       round(quantileDeterministicIf(0.5)(d.net_move_pct, cityHash64(toString(d.day)), d.day != w.witching_day), 2) AS other_days_median_net_move_pct
FROM daily AS d
INNER JOIN witching AS w ON toStartOfMonth(d.day) = w.m
GROUP BY w.witching_day
HAVING countIf(d.day != w.witching_day) > 0
ORDER BY w.witching_day

WITH sessions AS (
    SELECT b.day                                AS day,
           p.print_dollars                      AS close_dollars,
           b.continuous_dollars + p.print_dollars AS day_dollars
    FROM
    (
        SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS day,
               sum(toFloat64(close) * toFloat64(volume))            AS continuous_dollars
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'AMZN', 'GOOGL', 'META', 'AVGO', 'TSLA', 'JPM', 'LLY')
          AND window_start >= '2026-06-01 04:00:00'
          AND window_start <  '2026-07-01 04:00:00'
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
              + toMinute(toTimeZone(window_start, 'America/New_York')) BETWEEN 570 AND 959
        GROUP BY day
    ) AS b
    INNER JOIN
    (
        SELECT day, sum(print_dollars) AS print_dollars
        FROM
        (
            SELECT ticker,
                   toDate(toTimeZone(sip_timestamp, 'America/New_York'))  AS day,
                   toFloat64(argMaxIf(price, size, has(conditions, 8)))
                     * toFloat64(maxIf(size, has(conditions, 8)))         AS print_dollars
            FROM global_markets.stocks_trades
            WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'AMZN', 'GOOGL', 'META', 'AVGO', 'TSLA', 'JPM', 'LLY')
              AND sip_timestamp >= '2026-06-01 04:00:00'
              AND sip_timestamp <  '2026-07-01 04:00:00'
              AND toHour(toTimeZone(sip_timestamp, 'America/New_York')) = 16
              AND toMinute(toTimeZone(sip_timestamp, 'America/New_York')) < 10
            GROUP BY ticker, day
            HAVING countIf(has(conditions, 8)) > 0
        )
        GROUP BY day
    ) AS p ON p.day = b.day
)
SELECT round(anyIf(close_dollars, day = '2026-06-18') / 1e9, 1) AS witching_close_dollars_b,
       round(quantileDeterministicIf(0.5)(close_dollars, cityHash64(toString(day)), day != '2026-06-18') / 1e9, 1) AS other_june_median_close_dollars_b,
       round(anyIf(close_dollars, day = '2026-06-18')
             / quantileDeterministicIf(0.5)(close_dollars, cityHash64(toString(day)), day != '2026-06-18'), 2) AS times_median,
       round(anyIf(close_dollars / day_dollars, day = '2026-06-18') * 100, 1) AS witching_close_pct_of_day,
       round(quantileDeterministicIf(0.5)(close_dollars / day_dollars, cityHash64(toString(day)), day != '2026-06-18') * 100, 1) AS other_june_median_close_pct_of_day
FROM sessions
HAVING countIf(day != '2026-06-18') > 0

WITH spy_close AS (
    SELECT toFloat64(argMax(close, window_start)) AS px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2026-06-18 09:30:00', 'America/New_York')
      AND window_start < toDateTime('2026-06-18 16:00:00', 'America/New_York')
),
strikes AS (
    SELECT toFloat64(substring(ticker, length(ticker) - 7, 8)) / 1000 AS strike,
           sum(toFloat64(size)) AS contracts
    FROM global_markets.options_trades
    WHERE ticker LIKE 'O:SPY260618%'
      AND sip_timestamp >= '2026-06-18 08:00:00'
      AND sip_timestamp < '2026-06-19 01:00:00'
    GROUP BY strike
)
SELECT concat('$', toString(toInt32(s.strike))) AS strike,
       round(s.contracts / 1000, 1) AS contracts_k,
       round(s.strike - c.px, 2) AS strike_minus_close,
       round(abs(s.strike - c.px) / c.px * 100, 2) AS pct_from_close
FROM strikes AS s
CROSS JOIN spy_close AS c
ORDER BY s.contracts DESC, s.strike ASC
LIMIT 12