expiry_outcomes

WITH spy_by_day AS
(
    SELECT
        toDate(date)                             AS d,
        medianExact(toFloat64(underlying_close)) AS spot
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND date >= toDate('2026-06-01')
      AND date <  toDate('2026-07-01')
      AND underlying_close > 0
    GROUP BY d
),
last_sessions AS
(
    SELECT
        toDate(expiration_date) AS exp_date,
        max(toDate(date))       AS prior_session
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND expiration_date >= toDate('2026-06-01')
      AND expiration_date <  toDate('2026-07-01')
      AND date >= toDate('2026-05-22')
      AND date <  expiration_date
      AND volume > 0
    GROUP BY exp_date
),
atm AS
(
    SELECT
        toDate(g.expiration_date)                                                                                AS exp_date,
        argMin(toFloat64(g.strike_price), abs(toFloat64(g.strike_price) - toFloat64(g.underlying_close)))        AS strike,
        argMin(toFloat64(g.option_close), abs(toFloat64(g.strike_price) - toFloat64(g.underlying_close)))        AS premium_before
    FROM global_markets.options_greeks AS g
    INNER JOIN last_sessions AS ls
        ON ls.exp_date = toDate(g.expiration_date) AND ls.prior_session = toDate(g.date)
    WHERE g.underlying_symbol = 'SPY'
      AND lower(toString(g.option_type)) IN ('call', 'c')
      AND g.date >= toDate('2026-05-22')
      AND g.date <  toDate('2026-07-01')
      AND g.volume > 0
      AND g.option_close > 0
    GROUP BY exp_date
),
outcomes AS
(
    SELECT
        a.exp_date                                          AS exp_date,
        greatest(s.spot - a.strike, 0.0) / a.premium_before AS premium_ratio
    FROM atm AS a
    INNER JOIN spy_by_day AS s
        ON s.d = a.exp_date
)
SELECT
    tupleElement(b, 1)                                                                         AS outcome,
    countIf(o.premium_ratio >= tupleElement(b, 2) AND o.premium_ratio < tupleElement(b, 3))    AS expiries
FROM
(
    SELECT arrayJoin([
        ('1. Finished at or near zero (5% of the premium or less)', -1.0, 0.05),
        ('2. Lost more than half',                                   0.05, 0.5),
        ('3. Lost up to half',                                       0.5,  1.0),
        ('4. Gained, less than doubled',                             1.0,  2.0),
        ('5. Doubled or better',                                     2.0,  1000000.0)
    ]) AS b
) AS buckets
CROSS JOIN outcomes AS o
GROUP BY outcome
ORDER BY outcome