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 and Sortino 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()).

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 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.