ボックス・スプレッドと織り込みローン金利

WITH
    daily AS
    (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York'))   AS d,
            toFloat64(argMax(close, window_start))                 AS spy_close
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= '2026-06-01 00:00:00'
          AND window_start <  '2026-07-01 00:00:00'
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
               + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
               + toMinute(toTimeZone(window_start, 'America/New_York'))) < 960
        GROUP BY d
    ),
    strikes AS
    (
        SELECT
            round(avg(spy_close) / 5) * 5 - 5 AS lower_strike,
            round(avg(spy_close) / 5) * 5 + 5 AS upper_strike
        FROM daily
    ),
    levels AS
    (
        SELECT DISTINCT round(spy_close / 5) * 5 AS settle
        FROM daily
    )
SELECT
    concat('$', toString(toUInt32(settle)))                                                AS settle_level,
    concat('$', toString(toUInt32(lower_strike)), ' / $', toString(toUInt32(upper_strike))) AS box_strikes,
    round(greatest(settle - lower_strike, 0) - greatest(settle - upper_strike, 0), 2)      AS call_leg_value,
    round(greatest(upper_strike - settle, 0) - greatest(lower_strike - settle, 0), 2)      AS put_leg_value,
    round(greatest(settle - lower_strike, 0) - greatest(settle - upper_strike, 0)
        + greatest(upper_strike - settle, 0) - greatest(lower_strike - settle, 0), 2)      AS box_value
FROM levels
CROSS JOIN strikes
ORDER BY settle

WITH
    chain AS
    (
        SELECT
            toFloat64(strike_price)           AS strike,
            avg(toFloat64(underlying_close))  AS spot,
            round(avgIf(delta, delta > 0), 4) AS call_delta,
            round(avgIf(delta, delta < 0), 4) AS put_delta
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'SPY'
          AND date = '2026-06-01'
          AND expiration_date = '2026-12-18'
          AND iv_converged = 1
          AND volume > 0
        GROUP BY strike
        HAVING countIf(delta > 0) > 0
           AND countIf(delta < 0) > 0
    ),
    lower_leg AS
    (
        SELECT
            strike     AS k1,
            call_delta AS c1,
            put_delta  AS p1
        FROM chain
        ORDER BY abs(strike - spot)
        LIMIT 1
    )
SELECT
    toUInt32(strike - k1)                      AS strike_width,
    round(c1 - call_delta, 4)                  AS call_leg_delta,
    round(put_delta - p1, 4)                   AS put_leg_delta,
    round(c1 - call_delta + put_delta - p1, 4) AS box_net_delta
FROM chain
CROSS JOIN lower_leg
WHERE strike > k1
  AND strike <= k1 + 60
ORDER BY strike

WITH
    chain AS
    (
        SELECT
            multiIf(
                toFloat64(strike_price) / toFloat64(underlying_close) < 0.85, 1,
                toFloat64(strike_price) / toFloat64(underlying_close) < 0.95, 2,
                toFloat64(strike_price) / toFloat64(underlying_close) < 1.05, 3,
                toFloat64(strike_price) / toFloat64(underlying_close) < 1.15, 4,
                5)                          AS bucket_key,
            multiIf(
                bucket_key = 1, 'more than 15% below spot',
                bucket_key = 2, '5% to 15% below spot',
                bucket_key = 3, 'within 5% of spot',
                bucket_key = 4, '5% to 15% above spot',
                'more than 15% above spot') AS moneyness_bucket,
            volume
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'SPY'
          AND date >= '2026-06-01'
          AND date <  '2026-07-01'
          AND iv_converged = 1
          AND volume > 0
          AND days_to_expiry BETWEEN 30 AND 400
    ),
    totals AS
    (
        SELECT
            sum(volume) AS all_volume,
            count()     AS all_rows
        FROM chain
    )
SELECT
    moneyness_bucket,
    round(100 * count() / any(all_rows), 2)       AS contracts_pct,
    round(100 * sum(volume) / any(all_volume), 2) AS turnover_pct
FROM chain
CROSS JOIN totals
GROUP BY bucket_key, moneyness_bucket
ORDER BY bucket_key

SELECT
    concat(formatDateTime(expiration_date, '%b %e, %Y'),
           ' (', toString(toUInt32(any(days_to_expiry))), 'd)') AS expiry_label,
    round(100 * (0.10 / 10) * (365 / any(days_to_expiry)), 2)   AS annual_cost_pct
FROM global_markets.options_greeks
WHERE underlying_symbol = 'SPY'
  AND date = '2026-06-01'
  AND iv_converged = 1
  AND volume > 0
  AND toDayOfWeek(expiration_date) = 5
  AND toDayOfMonth(expiration_date) BETWEEN 15 AND 21
  AND days_to_expiry BETWEEN 30 AND 800
GROUP BY expiration_date
ORDER BY any(days_to_expiry)
LIMIT 12