2,236 answered market questions

WITH expiries AS (
    SELECT arrayJoin([toDate('2026-02-20'), toDate('2026-03-20'), toDate('2026-04-17'),
                      toDate('2026-05-15'), toDate('2026-06-18'), toDate('2026-07-17')]) AS expiry
),
chain AS (
    SELECT toDateOrNull(concat('20', substring(ticker, length(ticker) - 14, 6)))  AS expiry,
           intDiv(toUInt32OrZero(substring(ticker, length(ticker) - 7, 8)), 1000) AS strike,
           substring(ticker, length(ticker) - 8, 1)                              AS opt_type,
           sum(toFloat64(volume))                                                AS contracts
    FROM global_markets.options_minute_aggs
    WHERE ((window_start >= toDateTime('2026-02-16 08:00:00') AND window_start < toDateTime('2026-02-21 04:00:00'))
        OR (window_start >= toDateTime('2026-03-16 08:00:00') AND window_start < toDateTime('2026-03-21 04:00:00'))
        OR (window_start >= toDateTime('2026-04-13 08:00:00') AND window_start < toDateTime('2026-04-18 04:00:00'))
        OR (window_start >= toDateTime('2026-05-11 08:00:00') AND window_start < toDateTime('2026-05-16 04:00:00'))
        OR (window_start >= toDateTime('2026-06-15 08:00:00') AND window_start < toDateTime('2026-06-19 04:00:00'))
        OR (window_start >= toDateTime('2026-07-13 08:00:00') AND window_start < toDateTime('2026-07-18 04:00:00')))
      AND startsWith(ticker, 'O:SPY26')
      AND toDateOrNull(concat('20', substring(ticker, length(ticker) - 14, 6))) IN (SELECT expiry FROM expiries)
    GROUP BY expiry, strike, opt_type
),
curve AS (
    SELECT c1.expiry AS expiry,
           c1.strike AS settle,
           sum(multiIf(c2.opt_type = 'C' AND c2.strike < c1.strike, c2.contracts * (c1.strike - c2.strike),
                       c2.opt_type = 'P' AND c2.strike > c1.strike, c2.contracts * (c2.strike - c1.strike),
                       0)) AS payout
    FROM chain AS c1 INNER JOIN chain AS c2 ON c1.expiry = c2.expiry
    GROUP BY c1.expiry, c1.strike
),
pain AS (
    SELECT expiry, argMin(settle, payout) AS pain_strike FROM curve GROUP BY expiry
),
px AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           argMax(close, window_start)                          AS close_px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2026-02-05')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-07-17')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY d
),
prior AS (
    SELECT expiries.expiry AS expiry, argMax(px.close_px, px.d) AS week_earlier_px
    FROM expiries CROSS JOIN px
    WHERE px.d <= expiries.expiry - 7
    GROUP BY expiries.expiry
)
SELECT formatDateTime(pain.expiry, '%b %e')                                   AS expiry_label,
       pain.pain_strike                                                       AS min_payout_strike,
       round(px.close_px, 2)                                                  AS settlement_close,
       round(abs(px.close_px - pain.pain_strike) / px.close_px * 100, 2)      AS max_pain_miss_pct,
       round(abs(px.close_px - prior.week_earlier_px) / px.close_px * 100, 2) AS week_earlier_miss_pct
FROM pain
INNER JOIN px ON pain.expiry = px.d
INNER JOIN prior ON pain.expiry = prior.expiry
ORDER BY pain.expiry