Peak and Drawdown — Solution & ExplanationProblems / Peak and Drawdown / EditorialApproach

Running peak. MAX(value) OVER (PARTITION BY portfolio_id ORDER BY value_day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) — the expanding-window maximum, i.e. "the best day *up to now*".

Drawdown arithmetic. (peak - value) / peak * 100, rounded. New peaks self-report 0.00 because the row is inside its own frame.

Key InsightDrawdown is only meaningful causally — each day compared to the peak *as of that day* — and the expanding window frame is SQL's encoding of causality. The tempting GROUP BY maximum leaks the future: early days get judged against highs that hadn't occurred yet, understating historical risk. Running MIN/MAX extremes generalize the running-SUM intuition (record-to-date, worst-case-to-date, high-water-mark billing), and pairing the windowed extreme with plain row arithmetic afterward — CTE, then percentage — keeps the query legible instead of nesting windows inside expressions.Hints

'Highest value so far' is an aggregate over an expanding history — MAX with a running frame (UNBOUNDED PRECEDING to CURRENT ROW), partitioned per portfolio.

With the running frame, a new all-time high is its own peak, so (peak - value) is 0 and the drawdown prints 0.00 — no CASE required.

Don't use plain MAX ... GROUP BY: that's the END-of-history maximum, which would measure every day against a peak that may not have happened yet.

A recovery day (value rising but still below the old peak) must show a SHRINKING drawdown against the SAME peak — check the planted 90 -> 96 sequence behaves that way.

Solve Peak and Drawdown yourself →