Price return vs total return by holding period: S&P 500 tracker, windows ending July 31, 2026

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           argMax(toFloat64(close), window_start) AS close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2006-07-31')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-07-31')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY d
),
spans AS (
    SELECT y AS years,
           concat(toString(y), '-year') AS horizon,
           subtractYears(toDate('2026-07-31'), y) AS start_date
    FROM (SELECT arrayJoin([1, 3, 5, 10, 20]) AS y)
),
divs AS (
    SELECT dv.ex_dividend_date AS d,
           log(1 + toFloat64(dv.cash_amount) / dl.close) AS log_growth
    FROM global_markets.stocks_dividends AS dv
    INNER JOIN daily AS dl ON dl.d = dv.ex_dividend_date
    WHERE dv.ticker = 'SPY'
      AND dv.cash_amount > 0
      AND dv.ex_dividend_date <= toDate('2026-07-31')
),
endpoints AS (
    SELECT s.horizon AS horizon,
           s.years AS years,
           argMin(dl.close, dl.d) AS start_px,
           argMax(dl.close, dl.d) AS end_px
    FROM spans AS s, daily AS dl
    WHERE dl.d >= s.start_date
    GROUP BY s.horizon, s.years
),
reinvest AS (
    SELECT s.years AS years,
           exp(sum(dvs.log_growth)) AS factor
    FROM spans AS s, divs AS dvs
    WHERE dvs.d > s.start_date
    GROUP BY s.years
)
SELECT e.horizon AS horizon,
       round(100 * (e.end_px / e.start_px - 1), 1) AS price_return_pct,
       round(100 * (e.end_px / e.start_px * r.factor - 1), 1) AS total_return_pct,
       round(100 * (e.end_px / e.start_px) * (r.factor - 1), 1) AS dividend_points_pct
FROM endpoints AS e
INNER JOIN reinvest AS r ON e.years = r.years
ORDER BY e.years