Worst five-session stretch and deepest in-window drawdown, July 2025 to June 2026

WITH daily AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           toFloat64(argMax(close, window_start)) AS px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'KO', 'JNJ', 'AAPL', 'MSFT', 'NVDA', 'TSLA')
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2025-07-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-06-30')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, d
),
win AS (
    SELECT ticker, d, px,
           any(px) OVER (PARTITION BY ticker ORDER BY d
                         ROWS BETWEEN 5 PRECEDING AND 5 PRECEDING) AS px_5_ago,
           max(px) OVER (PARTITION BY ticker ORDER BY d
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_high
    FROM daily
)
SELECT ticker,
       round(min(100 * (px / px_5_ago - 1)), 2) AS worst_5_session_pct,
       round(min(100 * (px / running_high - 1)), 2) AS deepest_drawdown_pct,
       round(100 * countIf(px / running_high - 1 <= -0.1) / count(), 1) AS pct_days_10pct_below_high
FROM win
WHERE isFinite(px_5_ago)
GROUP BY ticker
ORDER BY deepest_drawdown_pct