Historical volatility
This page shows how to compute the rolling 30-day realised volatility of a crypto pair entirely server-side, using two of the most common estimators:
Close-to-close — the textbook approach: standard deviation of daily log returns, annualised by
√365.Parkinson — uses the daily range (high/low) instead of just closes. Lower variance estimator because it sees more of the intraday move.
The full pipeline runs in a single SQL query against api.ohlcv(...). We walk through it CTE-by-CTE then provide the full code at the end. 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 13 month and toStartOfDay(now()).
Select the data
We pull daily candles for BTC-USDT on binance for the pinned window, with named bindings for the date range, window length, and annualisation factor.
WITH
'2024-01-01' AS start_date,
'2024-12-31' AS end_date,
30 AS window_days,
365 AS sessions_per_year,
candles AS (
SELECT toDate(start) AS day, open, high, low, close
FROM api.ohlcv(candle_duration_in_minutes = 1440)
WHERE exchange = 'binance'
AND market = 'BTC-USDT'
AND start >= start_date
AND start <= end_date
)Functions used: toDate, ln, nullIf, lagInFrame, pow, stddevSamp, sqrt
candles contains:
2024-01-01
42283.58
44184.10
42180.77
44179.55
2024-01-02
44179.55
45879.63
44148.34
44946.91
2024-01-03
44946.91
45500.00
40750.00
42845.23
2024-01-04
42845.23
44729.58
42613.77
44151.10
...
...
...
...
...
Compute log returns and intraday range
Two per-row quantities feed the two estimators:
log_return = ln(close / close[t-1])— close-to-close log return.hl_log_sq = ln(high / low)²— squared log range, the input to Parkinson.
We use nullIf(..., 0) on the lagged close so the very first row (no predecessor) yields NULL rather than tripping a division-by-zero on Decimal(76, 20).
returns contains:
2024-01-01
44179.55
NULL
0.002153
2024-01-02
44946.91
0.017220
0.001480
2024-01-03
42845.23
-0.047888
0.012156
2024-01-04
44151.10
0.030024
0.002348
...
...
...
...
Roll the 30-day window
ClickHouse window functions compute both estimators in one pass:
Close-to-close:
stddevSamp(log_return)over the 30-row window, annualised by√365and reported in percent.Parkinson:
√( Σ ln(H/L)² / (4·N·ln 2) )over the same window, also annualised.
We carry count(log_return) OVER w so we can drop early rows where the window isn't yet full (the first 30 days, before we have 30 valid log returns).
Output:
2024-01-31
53.90
58.88
2024-02-01
53.70
58.73
2024-02-02
51.02
54.19
...
...
...
2024-12-29
44.39
56.48
2024-12-30
44.37
56.94
2024-12-31
44.38
57.53
Notes on the estimators
stddevSamp(Bessel-corrected,ddof=1) matches the convention used by most published HV charts — including Deribit's reference implementation andpandas.rolling.std()with default arguments. UsestddevPopif you prefer the population estimator.√365annualisation is appropriate for crypto (24/7 trading). For traditional markets, swap in√252.The Parkinson constant
1 / (4·N·ln 2)≈0.3607 / N— switch the window length only by changing thewindow_daysbinding.
Full example
The whole pipeline runs in well under a second of compute time; round-trip latency dominates.
1 year
~ 5–8 s
5 years
~ 7–10 s
The dataset is downloadable as-is by running the candles sub-query if you prefer to compute the volatilities client-side (see the companion Python reference for a reproducible parity baseline).
See also
Backtesting a simple SMA strategy — uses the same
api.ohlcv(...)view andlagInFramewindow pattern.Source article that inspired this example: Historical volatility calculations (Deribit Insights).
Last updated
Was this helpful?