Max drawdown in one SQL window query

Drawdown — how far you are below the highest point so far — sounds like a job for pandas' cummax(). But if the prices already live in a database, the round-trip through Python is wasted motion. A window function does it in place:

WITH equity AS (
  SELECT trade_date, close_price,
         MAX(close_price) OVER (ORDER BY trade_date) AS peak
  FROM equity_prices
  WHERE ticker = 'SPY'
)
SELECT trade_date, close_price, peak,
       ROUND((close_price - peak) * 100.0 / peak, 2) AS drawdown_pct
FROM equity
ORDER BY drawdown_pct
LIMIT 5;

The whole trick is one clause: MAX(close_price) OVER (ORDER BY trade_date). When a window has an ORDER BY but no explicit frame, SQL defaults the frame to "from the first row up to the current row" — so this MAX is a running maximum, the peak so far, not the global maximum. Subtract, divide, and every row knows how deep under water it is. The final ORDER BY surfaces the five worst days; the maximum drawdown is row one.

Why this beats pulling the data out:

• It runs where the data is. On a few million rows, shipping prices to a notebook to compute one number is the slowest part of the job.
• It composes. Add PARTITION BY ticker before the ORDER BY and you get per-asset drawdowns for the whole book in the same single pass — the SQL equivalent of a groupby-cummax.
• It is auditable. A risk figure defined in one declarative query is easier to sign off than a notebook with hidden state.

Two traps: don't write MAX(...) OVER () (empty parentheses = global max, which silently turns drawdown-so-far into drawdown-from-the-final-peak); and keep the division floating-point (* 100.0, not * 100) — integer division has ruined more risk reports than bad math ever did.

You can run this exact query against a two-year, five-ticker price table in your browser — no setup — in the SQL sandbox below.