2,170 answered market questions

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
           argMax(toFloat64(close), window_start) AS close_px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2016-07-01')
      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 session_date
),
scoped AS (
    SELECT arrayJoin([1, 2, 3, 5, 10]) AS lookback_years,
           session_date,
           close_px
    FROM daily
),
windowed AS (
    SELECT lookback_years, session_date, close_px
    FROM scoped
    WHERE session_date >= subtractYears(toDate('2026-07-31'), lookback_years)
),
runs AS (
    SELECT lookback_years,
           session_date,
           close_px,
           max(close_px) OVER (PARTITION BY lookback_years ORDER BY session_date
                               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_peak
    FROM windowed
)
SELECT concat(toString(lookback_years), if(lookback_years = 1, ' year', ' years')) AS lookback,
       round(100 * max(1 - close_px / running_peak), 1) AS max_drawdown_pct,
       formatDateTime(argMax(session_date, 1 - close_px / running_peak), '%b %Y') AS worst_point
FROM runs
GROUP BY lookback_years
ORDER BY lookback_years