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 (in the SMA backtest) and Maximum drawdown & Calmar.

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

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, toNullable, stddevSamp, 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).