# 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](/compute-engine/backtesting-simple-sma-strategy.md#sharpe-ratio) and [Sortino](/compute-engine/sortino-ratio.md) ratios — same numerator family, different risk denominator.

## Query

The interesting SQL pattern is the **running peak** — `max(cum_return)` 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
```

The first three CTEs (`candles`, `market_returns`, `all_returns`) mirror the [Sortino](/compute-engine/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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.koinju.io/compute-engine/max-drawdown-calmar.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.