sp 500 — 3 answered market questions

SPY total return since 2016, after removing the best single days

most recentas of 2026-07-16 ranking 5×2read in context →

SPY total return since 2016, after removing the best single days — 5 rows by 2 columns, computed 2026-07-16 from US exchange, SIP and OPRA data.
best_days_missed	total_return_pct
0	275.5
10	97
20	42.4
30	9
50	-32.5

the exact SQL behind every number

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS dt,
           argMax(toFloat64(close), toTimeZone(window_start, 'America/New_York')) AS c
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY' AND window_start >= '2016-01-01'
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY dt
),
rets AS (
    SELECT dt, c / lagInFrame(c) OVER (ORDER BY dt) - 1 AS ret FROM daily
),
ranked AS (
    SELECT log(1 + ret) AS lr, row_number() OVER (ORDER BY ret DESC) AS rnk
    FROM rets
    WHERE ret IS NOT NULL AND ret > -0.5 AND ret < 0.5
)
SELECT k AS best_days_missed,
       round((exp(sumIf(lr, rnk > k)) - 1) * 100, 1) AS total_return_pct
FROM ranked
CROSS JOIN (SELECT arrayJoin([0, 10, 20, 30, 50]) AS k) AS kvals
GROUP BY k
ORDER BY k

More from this analysisWhat Missing the Best Days Costs

SPY's 20 best and 20 worst days since 2016, counted by year ranking 11×3 → The ten biggest single-day gains for SPY since 2016 ranking 10×2 → S&P 500 worst intra-year drawdown vs the year's price return, since 2016 ranking 11×3 → How far below its high the S&P 500 sits: share of trading days since 2016 ranking 5×2 → See all 2,170 queries →