> For the complete documentation index, see [llms.txt](https://docs.koinju.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.koinju.io/sql-cookbook/max-drawdown-calmar.md).

# Maximum drawdown & Calmar ratio

**Maximum drawdown** is the largest peak-to-trough loss in a price series. The **Calmar ratio** divides the annualised return by `|max_drawdown|`, the natural sibling to the [Sharpe](/sql-cookbook/backtesting-simple-sma-strategy.md#sharpe-ratio) and [Sortino](/sql-cookbook/sortino-ratio.md) ratios — same numerator family, different risk denominator.

## Query

The interesting SQL pattern is the **running peak** — [`max(cum_return)`](https://clickhouse.com/docs/sql-reference/window-functions) over an `UNBOUNDED PRECEDING` frame, equivalent to pandas' `.expanding().max()`. We also lean on `∏(1 + r) ≡ exp(Σ log(1 + r))` because ClickHouse has no `cumprod`.

The example is pinned to `2024-01-01` → `2024-12-31` to match the parity-tested baseline; for live data swap the two date literals for `now() - interval 12 month` and `toStartOfDay(now())`.

```sql
WITH
    '2024-01-01' AS start_date,
    '2024-12-31' AS end_date,
    365 AS sessions_per_year,
    candles AS (
        SELECT toDate(start) AS day, market, toFloat64(close) AS close
        FROM api.ohlcv(candle_duration_in_minutes = 1440)
        WHERE exchange = 'binance'
          AND market IN ('BTC-USDT', 'ETH-USDT', 'SOL-USDT', 'ADA-USDT', 'DOGE-USDT')
          AND start BETWEEN start_date AND end_date
    ),
    market_returns AS (
        SELECT day, market,
               close / lagInFrame(toNullable(close), 1) OVER (PARTITION BY market ORDER BY day) - 1 AS ret
        FROM candles
    ),
    all_returns AS (
        SELECT day, market, ret FROM market_returns WHERE ret IS NOT NULL
        UNION ALL
        SELECT day, 'PORTFOLIO' AS market, avg(ret) AS portfolio_ret
        FROM market_returns WHERE ret IS NOT NULL GROUP BY day
    ),
    cumulative AS (
        SELECT market, day, ret, exp(sum(log(1 + ret)) OVER w) AS cum_return
        FROM all_returns
        WINDOW w AS (PARTITION BY market ORDER BY day
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    ),
    drawdowns AS (
        SELECT market, ret, cum_return / max(cum_return) OVER w - 1 AS drawdown
        FROM cumulative
        WINDOW w AS (PARTITION BY market ORDER BY day
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    )
SELECT market,
       min(drawdown) AS max_drawdown,
       avg(ret) * sessions_per_year / abs(min(drawdown)) AS calmar
FROM drawdowns
GROUP BY market
ORDER BY market
```

Functions used: [`lagInFrame`](https://clickhouse.com/docs/sql-reference/window-functions#laginframe), [`toNullable`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#tonullable), [`exp`](https://clickhouse.com/docs/sql-reference/functions/math-functions#exp), [`log`](https://clickhouse.com/docs/sql-reference/functions/math-functions#log), [`abs`](https://clickhouse.com/docs/sql-reference/functions/arithmetic-functions#abs)

The first three CTEs (`candles`, `market_returns`, `all_returns`) mirror the [Sortino](/sql-cookbook/sortino-ratio.md#query) page — fetch closes, cast to `Float64`, compute simple per-market returns with the `lagInFrame(toNullable(close), 1)` trick to get `NULL` (not `0`) on the first row of each partition, then synthesise the equal-weight `PORTFOLIO` row directly inside the `UNION ALL`.

The two new CTEs are where the work happens:

* `cumulative.cum_return` — running compounded growth. `exp(sum(log(1 + ret)))` over an unbounded preceding frame is mathematically identical to a cumulative product and numerically stable. ClickHouse has no `cumprod` window aggregate (Postgres, DuckDB, BigQuery don't either) — the log-sum-exp form is the category-wide workaround.
* `drawdowns.drawdown` — current `cum_return` over its running peak, minus one. Always `≤ 0` by construction; `min(drawdown)` per market is the max-drawdown.

## Output (2024)

For `BTC-USDT`, the rolling values look like:

| day        | cum\_return | drawdown |
| ---------- | ----------- | -------- |
| 2024-01-02 | 1.01737     | 0.00000  |
| 2024-03-13 | 1.71117     | 0.00000  |
| 2024-08-05 | 1.27148     | -0.25696 |
| 2024-12-31 | 2.20847     | 0.00000  |

A unit of starting capital ended at \~2.21× by year-end (the +120 % BTC year of 2024). The deepest drawdown was \~26 % from the March peak.

Final ranking:

| market    | max\_drawdown | calmar |
| --------- | ------------- | ------ |
| BTC-USDT  | -0.2615       | 3.4029 |
| DOGE-USDT | -0.5794       | 3.0152 |
| SOL-USDT  | -0.3823       | 2.2981 |
| PORTFOLIO | -0.4207       | 2.2469 |
| ETH-USDT  | -0.4526       | 1.2305 |
| ADA-USDT  | -0.5978       | 1.0943 |

BTC tops Calmar despite a smaller annualised return than DOGE — its drawdown is by far the shallowest, so per-unit-of-tail-risk it's the strongest.

| basket size × period | execution time |
| -------------------- | -------------- |
| 5 markets × 1 year   | \~20–30 s      |

Most of the time is in the two `UNBOUNDED PRECEDING` windows. For a single-asset Calmar, drop the `'PORTFOLIO'` half of `all_returns` and the query collapses further.