Model decay per calendar step into the July 17, 2026 expiration (hypothetical at-the-money option)

WITH sessions AS
(
    SELECT date
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2026-06-15'
      AND date <= '2026-07-14'
    GROUP BY date
),
stepped AS
(
    SELECT
        date,
        dateDiff('day', date, toDate('2026-07-17')) AS days_left,
        dateDiff('day', date, leadInFrame(date) OVER (ORDER BY date ASC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)) AS step_days
    FROM sessions
)
SELECT
    toString(date)                   AS session_date,
    formatDateTime(date, '%a %b %e') AS session_label,
    step_days                        AS calendar_days_to_next_session,
    round(100 * (1 - sqrt(toFloat64(days_left - 1) / toFloat64(days_left))), 2)         AS one_day_pct,
    round(100 * (1 - sqrt(toFloat64(days_left - step_days) / toFloat64(days_left))), 2) AS actual_step_pct
FROM stepped
WHERE date <= '2026-07-10'
  AND step_days BETWEEN 1 AND 10
ORDER BY date ASC