Write analytical queries

ClickHouse speaks SQL, but analytical queries often scan long time ranges and aggregate millions of rows. Select only the columns you need and filter on fields supported by the ordering key. SELECT * on a columnar store throws away its main advantage — every column you name is a file ClickHouse has to read.

Here’s the workhorse pattern, an hourly rollup for one service:

SELECT
  toStartOfHour(ts) AS hour,
  count() AS requests,
  countIf(status >= 500) / count() AS error_rate,
  quantile(0.95)(duration_ms) AS p95_ms
FROM events
WHERE service = 'api'
  AND ts >= now() - INTERVAL 24 HOUR
GROUP BY hour
ORDER BY hour;

Three ClickHouse idioms are doing the work.

toStartOfHour(ts) truncates each timestamp to its hour, turning a raw event stream into time buckets. The whole family exists: toStartOfDay, toStartOfWeek, toStartOfInterval for custom windows.

countIf(status >= 500) is a conditional aggregate — it counts only rows matching the condition, in one pass. Most aggregates have an -If variant (sumIf, avgIf), which replaces the self-joins or CASE pyramids you’d write elsewhere.

quantile(0.95)(duration_ms) computes an approximate 95th percentile. Approximate is the default posture here: uniq(user_id) estimates distinct counts with a small, bounded error and runs far faster than uniqExact(user_id). For a dashboard, trading a fraction of a percent of accuracy for a big speedup is almost always right; use the exact versions when the number feeds billing or an SLA.

Keep units visible

Label units in names and results. duration_ms, p95_ms, bytes_out — the suffix travels with the column into dashboards and CSV exports. An unlabeled p95 of 310 will eventually be read as seconds by someone, and that someone will page you.

The same discipline applies to rates: error_rate above is a fraction between 0 and 1. If a chart needs percent, multiply at the display layer, not in the query that other queries copy from.

Verify against a dataset you can count by hand

Build hourly request counts, error rates, and duration percentiles for one service. Verify each result against a tiny hand-checked dataset:

INSERT INTO events VALUES
  ('2026-08-03 10:05:00', 'api', 1, 'request', 40,  200),
  ('2026-08-03 10:20:00', 'api', 2, 'request', 60,  200),
  ('2026-08-03 10:40:00', 'api', 3, 'request', 300, 500);

Three rows, one hour: the count must be 3 and the error rate 0.333. If the query is wrong on three rows you can check mentally, it’s wrong on three billion — you just wouldn’t have noticed. Validate the logic small, then point it at the real table.