expiry_day

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,
        argMin(toFloat64(g.underlying_close), abs(toFloat64(g.strike_price) - toFloat64(g.underlying_close)))    AS spy_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
)
SELECT
    toString(a.exp_date)                                                                   AS expiry_date,
    concat(formatDateTime(a.exp_date, '%a %b '), toString(toDayOfMonth(a.exp_date)))       AS expiry_label,
    round(a.premium_before, 2)                                                             AS premium_prior_close,
    round(greatest(s.spot - a.strike, 0.0), 2)                                             AS value_at_expiry,
    round(greatest(s.spot - a.strike, 0.0) / a.premium_before * 100, 0)                    AS pct_of_premium_left,
    round((s.spot / a.spy_before - 1) * 100, 2)                                            AS spy_move_pct
FROM atm AS a
INNER JOIN spy_by_day AS s
    ON s.d = a.exp_date
ORDER BY a.exp_date