日经225期权与SQ结算机制

WITH sessions AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
           argMin(open, window_start) AS session_open,
           argMax(close, window_start) AS session_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2024-12-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-06-30')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY session_date
),
paired AS (
    SELECT session_date,
           toFloat64(session_open) AS open_px,
           toFloat64(session_close) AS close_px,
           any(toFloat64(session_close)) OVER (ORDER BY session_date ASC
                                               ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prior_close
    FROM sessions
)
SELECT formatDateTime(session_date, '%Y-%m-%d') AS date,
       formatDateTime(session_date, '%b %e, %Y') AS friday_label,
       round(100 * (open_px - prior_close) / prior_close, 2) AS open_gap_pct,
       round(100 * (close_px - open_px) / open_px, 2) AS open_to_close_pct
FROM paired
WHERE prior_close > 0
  AND session_date >= toDate('2025-01-01')
  AND toDayOfWeek(session_date) = 5
  AND toDayOfMonth(session_date) BETWEEN 15 AND 21
ORDER BY session_date

WITH px AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
           argMin(open, window_start) AS session_open,
           argMax(close, window_start) AS session_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AAPL','MSFT','NVDA','AMZN','GOOGL','META','JPM','XOM','JNJ','KO','WMT','CAT')
      AND toDate(toTimeZone(window_start, 'America/New_York')) IN (toDate('2026-05-14'), toDate('2026-05-15'))
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, session_date
)
SELECT ticker,
       round(100 * (toFloat64(anyIf(session_open, session_date = toDate('2026-05-15')))
                    / toFloat64(anyIf(session_close, session_date = toDate('2026-05-14'))) - 1), 2) AS open_gap_pct,
       round(100 * (toFloat64(anyIf(session_close, session_date = toDate('2026-05-15')))
                    / toFloat64(anyIf(session_open, session_date = toDate('2026-05-15'))) - 1), 2) AS open_to_close_pct
FROM px
GROUP BY ticker
HAVING countIf(session_date = toDate('2026-05-14')) = 1
   AND countIf(session_date = toDate('2026-05-15')) = 1
ORDER BY open_gap_pct DESC

SELECT days_to_expiry AS dte,
       round(quantileDeterministic(0.5)(toFloat64(abs(theta)), cityHash64(ticker)), 3) AS median_theta_usd,
       round(quantileDeterministic(0.5)(toFloat64(implied_volatility) * 100, cityHash64(ticker)), 1) AS median_iv_pct,
       count() AS contract_count
FROM global_markets.options_greeks
WHERE underlying_symbol = 'SPY'
  AND date >= toDate('2026-01-02')
  AND date <= toDate('2026-06-30')
  AND iv_converged = 1
  AND volume > 0
  AND days_to_expiry BETWEEN 1 AND 60
  AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.02
GROUP BY dte
HAVING count() >= 30
ORDER BY dte

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
           argMax(close, window_start) AS session_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2024-08-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-07-31')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY session_date
),
rets AS (
    SELECT session_date,
           toFloat64(session_close) AS close_px,
           any(toFloat64(session_close)) OVER (ORDER BY session_date ASC
                                               ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prior_close
    FROM daily
),
realized AS (
    SELECT toStartOfMonth(session_date) AS month_start,
           round(stddevSamp(close_px / prior_close - 1) * sqrt(252) * 100, 1) AS realized_vol_pct,
           count() AS sessions
    FROM rets
    WHERE prior_close > 0
    GROUP BY month_start
),
implied AS (
    SELECT toStartOfMonth(date) AS month_start,
           addMonths(toStartOfMonth(date), 1) AS next_month_start,
           round(quantileDeterministic(0.5)(toFloat64(implied_volatility) * 100, cityHash64(ticker)), 1) AS implied_vol_pct
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND date >= toDate('2024-08-01')
      AND date <= toDate('2026-06-30')
      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.05
    GROUP BY month_start, next_month_start
)
SELECT formatDateTime(i.month_start, '%Y-%m') AS month,
       i.implied_vol_pct AS implied_vol_pct,
       r.realized_vol_pct AS realized_vol_pct,
       round(i.implied_vol_pct - r.realized_vol_pct, 1) AS premium_pct,
       round(avg(i.implied_vol_pct - r.realized_vol_pct) OVER (), 1) AS avg_premium_pct
FROM implied AS i
INNER JOIN realized AS r ON i.next_month_start = r.month_start
WHERE r.sessions >= 15
ORDER BY month

WITH daily AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
           argMax(close, window_start) AS session_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY','QQQ','IWM','AAPL','MSFT','NVDA')
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2025-06-20')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-06-30')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, session_date
),
rets AS (
    SELECT ticker,
           session_date,
           toFloat64(session_close) AS close_px,
           any(toFloat64(session_close)) OVER (PARTITION BY ticker ORDER BY session_date ASC
                                               ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prior_close
    FROM daily
),
realized AS (
    SELECT ticker,
           round(stddevSamp(close_px / prior_close - 1) * sqrt(252) * 100, 1) AS realized_vol_pct,
           count() AS sessions
    FROM rets
    WHERE prior_close > 0
      AND session_date >= toDate('2025-07-01')
    GROUP BY ticker
),
implied AS (
    SELECT underlying_symbol AS underlying,
           round(quantileDeterministic(0.5)(toFloat64(implied_volatility) * 100, cityHash64(ticker)), 1) AS implied_vol_pct
    FROM global_markets.options_greeks
    WHERE underlying_symbol IN ('SPY','QQQ','IWM','AAPL','MSFT','NVDA')
      AND date >= toDate('2025-07-01')
      AND date <= toDate('2026-06-30')
      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.05
    GROUP BY underlying
)
SELECT i.underlying AS ticker,
       i.implied_vol_pct AS implied_vol_pct,
       r.realized_vol_pct AS realized_vol_pct,
       round(i.implied_vol_pct - r.realized_vol_pct, 1) AS premium_pct
FROM implied AS i
INNER JOIN realized AS r ON i.underlying = r.ticker
WHERE r.sessions >= 200
ORDER BY premium_pct DESC