Write analytical queries

ClickHouse speaks SQL, but analytical queries scan long time ranges and aggregate millions of rows. Two habits keep them fast. Select only the columns you need, and filter on columns the ordering key can help with.

Our events table needs one more column for this lesson, the HTTP status of each request:

ALTER TABLE events ADD COLUMN status UInt16;

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 do the work here.

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

countIf(status >= 500) is a conditional aggregate. It counts only the rows matching the condition, in one pass. Most aggregates have an -If variant, like sumIf and avgIf. They replace the self-joins and CASE pyramids you’d write in other databases.

quantile(0.95)(duration_ms) computes an approximate 95th percentile. Approximate is the default posture in ClickHouse. 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

Put the unit in the name. 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 goes for rates. error_rate above is a fraction between 0 and 1. If a chart needs a percentage, multiply at the display layer, not in the query that everyone else copies from.

Check the math on data you can count by hand

Before pointing a query at the real table, run it against three rows you can verify in your head:

INSERT INTO events (ts, service, user_id, event_type, duration_ms, status) 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 too. You just wouldn’t have noticed.

I do this for every new aggregation. Validate the logic small, then point it at the real table.