The price of time: at-the-money SPY call price by DTE, midday July 8, 2026

WITH spot AS (
    SELECT avg(close) AS px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2026-07-08 11:30:00', 'America/New_York')
      AND window_start < toDateTime('2026-07-08 13:00:00', 'America/New_York')
),
chain AS (
    SELECT toDateOrNull(concat('20', substring(ticker, length(ticker) - 14, 6))) AS expiry,
           toFloat64(substring(ticker, length(ticker) - 7, 8)) / 1000 AS strike,
           avg(close) AS opt_px
    FROM global_markets.options_minute_aggs
    WHERE ticker LIKE 'O:SPY2%'
      AND substring(ticker, length(ticker) - 8, 1) = 'C'
      AND window_start >= toDateTime('2026-07-08 11:30:00', 'America/New_York')
      AND window_start < toDateTime('2026-07-08 13:00:00', 'America/New_York')
    GROUP BY expiry, strike
    HAVING sum(volume) > 0
       AND abs(strike - (SELECT px FROM spot)) <= 3
       AND dateDiff('day', toDate('2026-07-08'), expiry) >= 0
),
atm AS (
    SELECT dateDiff('day', toDate('2026-07-08'), expiry) AS dte,
           argMin(opt_px, (abs(strike - (SELECT px FROM spot)), strike)) AS call_price
    FROM chain
    GROUP BY expiry
),
targets AS (
    SELECT arrayJoin([0, 1, 2, 7, 14, 30, 60, 90, 180, 365, 730]) AS target
),
nearest AS (
    SELECT argMin(dte, (abs(dte - target), dte)) AS pick_dte,
           argMin(call_price, (abs(dte - target), dte)) AS pick_px
    FROM targets CROSS JOIN atm
    GROUP BY target
)
SELECT concat(toString(pick_dte), ' DTE') AS days_to_expiration,
       round(pick_px, 2) AS atm_call_price,
       round(pick_px / greatest(pick_dte, 1), 2) AS premium_per_remaining_day
FROM nearest
GROUP BY days_to_expiration, atm_call_price, premium_per_remaining_day, pick_dte
ORDER BY pick_dte