clickhouse-quant

name	clickhouse-quant
description	Query craft for the QuantLab ClickHouse warehouse of crypto OHLCV candles. Load this when writing SQL against the `quantlab.ohlcv` table — backtests, volatility, returns, drawdowns, time-of-day effects. Covers the ReplacingMergeTree layout, window-function patterns, and the `tools/ch.py` query tool.

clickhouse-quant

A skill for running quantitative crypto research against ClickHouse. This mirrors the project AGENTS.md so it works even if installed globally (~/.pi/agent/skills/clickhouse-quant/) or shared as a Pi package.

Tool

If the host project registers a native clickhouse_query tool (QuantLab's Pi extension does), call it with a single SQL statement. Otherwise run SQL through the bundled CLI via bash:

python3 tools/ch.py "<SQL>"

Either way you get the result table plus a [speed] line (rows scanned + ms), and both run read-only (writes/DDL are rejected). Always show the user the SQL and the speed line. Never fabricate numbers.

Table: quantlab.ohlcv

symbol, exchange, interval are LowCardinality(String); ts is DateTime('UTC'); open/high/low/close/volume are Float64; ingested_at is a DateTime version column.

Engine is ReplacingMergeTree(ingested_at) ordered by (symbol, exchange, interval, ts); the latest fetch wins on dedup. Add FINAL for exact-once rows if the ingest ran more than once. (coingecko rows are coarse 4d candles with volume = 0.)

Patterns

Daily returns with a named window (nullIf(lagInFrame(close), 0) so the first row's return is NULL, not log(close/0) = +inf):

SELECT ts, close,
       log(close / nullIf(lagInFrame(close) OVER w, 0)) AS ret
FROM ohlcv
WHERE symbol = 'BTC' AND exchange = 'kraken' AND interval = '1d'
WINDOW w AS (PARTITION BY symbol, exchange, interval ORDER BY ts)
ORDER BY ts;

Annualized realized volatility, ranked (stddevSamp ignores the first-row NULL, so no +inf leaks in; filter one exchange so series don't interleave):

WITH r AS (
  SELECT symbol,
         log(close / nullIf(lagInFrame(close) OVER w, 0)) AS ret
  FROM ohlcv
  WHERE interval = '1d' AND exchange = 'kraken'
  WINDOW w AS (PARTITION BY symbol, exchange, interval ORDER BY ts)
)
SELECT symbol, round(stddevSamp(ret) * sqrt(365), 3) AS ann_vol
FROM r
GROUP BY symbol ORDER BY ann_vol DESC;

SMA crossover signal:

SELECT ts, close,
       avg(close) OVER (PARTITION BY symbol, exchange, interval ORDER BY ts ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS sma20,
       avg(close) OVER (PARTITION BY symbol, exchange, interval ORDER BY ts ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) AS sma50
FROM ohlcv WHERE symbol = 'ETH' AND exchange = 'kraken' AND interval = '1d'
ORDER BY ts;

Guardrails

Research/backtesting only. Not financial advice; do not recommend trades or predict prices. State backtest assumptions (no fees/slippage, close fills).