One decade of S&P 500 daily returns compounded at eight fixed bet sizes: ending wealth and worst drawdown

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS dt,
           argMax(toFloat64(close), window_start) AS c
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= '2016-01-01 00:00:00'
      AND window_start <  '2026-01-01 00:00:00'
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY dt
),
steps AS (
    SELECT dt, c,
           lagInFrame(c) OVER (ORDER BY dt ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev
    FROM daily
),
rets AS (
    SELECT dt, c / prev - 1 AS ret
    FROM steps
    WHERE prev > 0
),
sizes AS (
    SELECT arrayJoin([0.5, 1.0, 1.5, 2.0, 2.5, 3.0, 4.0, 5.0]) AS bet
),
paths AS (
    SELECT bet, dt,
           exp(sum(log(greatest(1 + bet * ret, 0.0001)))
               OVER (PARTITION BY bet ORDER BY dt
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS equity
    FROM rets CROSS JOIN sizes
),
peaks AS (
    SELECT bet, dt, equity,
           max(equity) OVER (PARTITION BY bet ORDER BY dt
                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS peak
    FROM paths
)
SELECT concat(toString(bet), 'x') AS bet_size,
       round(argMax(equity, dt), 2) AS ending_multiple,
       round(100 * max(1 - equity / peak), 1) AS max_drawdown_pct
FROM peaks
GROUP BY bet
ORDER BY bet