> 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/sortino-ratio.md).

# Sortino ratio

The Sortino ratio is a downside-aware variant of the Sharpe ratio: instead of the std of *all* returns in the denominator, it uses the std of just the *negative* ones — punishing downside volatility specifically.

Siblings: [Sharpe](/sql-cookbook/backtesting-simple-sma-strategy.md#sharpe-ratio) (in the SMA backtest) and [Maximum drawdown & Calmar](/sql-cookbook/max-drawdown-calmar.md).

## Query

We rank the five top crypto majors plus an equal-weight portfolio in a single CTE chain, computing daily simple returns from `api.ohlcv(...)` and applying

```
sortino = (E[r] · 365 − 0.01) / (σ_neg · √365)
```

over the full year, with a 1 % annualised risk-free rate (`0.01`) in the numerator. `r` = `(close − close[t-1]) / close[t-1]`, `σ_neg` = sample std (`stddevSamp`) of just the negative subset.

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,
    0.01 AS risk_free_rate,
    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
    )
SELECT market,
       (avg(ret) * sessions_per_year - risk_free_rate)
       / (stddevSamp(if(ret < 0, ret, NULL)) * sqrt(sessions_per_year)) AS sortino
FROM all_returns
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), [`stddevSamp`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/stddevsamp), [`if`](https://clickhouse.com/docs/sql-reference/functions/conditional-functions#if)

## Output (2024)

| market    | sortino |
| --------- | ------- |
| DOGE-USDT | 3.0529  |
| BTC-USDT  | 2.7336  |
| PORTFOLIO | 2.2168  |
| SOL-USDT  | 1.8328  |
| ETH-USDT  | 1.3739  |
| ADA-USDT  | 1.2769  |

The portfolio's `2.22` lands between BTC and SOL — diversification smooths the downside-only variance.

Bump `risk_free_rate` to whichever annualised reference you prefer (e.g. `0.04` for \~4 % T-bill, `0` to drop the term entirely).