Typorithm · easy

Aggregate Functions

aggregate-functionsclickhousegroup-byuniqquantile

Saved on this device

Full lesson · 8 learning steps Open any exercise for focused practice

Step 1 of 8 · intro card

Learn Aggregate Functions

Querying

Aggregate per group with ClickHouse functions: count() and sum() for totals, uniq() for fast approximate distinct counts, and quantile(0.95)(col) for percentiles.

GROUP BY collapses many rows into one row per key, and aggregate functions summarize each group.

uniq() gives a fast approximate distinct count, while uniqExact() is precise but heavier on memory.

quantile(0.95)(col) returns an approximate percentile — ideal for latency p95 and p99 dashboards.

count(), sum(), avg() for basic aggregates.

uniq() approximate vs uniqExact() exact.

quantile(0.95)(col) for percentiles.

SELECT
    host,
    count() AS requests,
    uniq(user_id) AS approx_users,
    quantile(0.95)(latency) AS p95
FROM metrics
GROUP BY host;

Read the implementation

Connect each part of the code to the concept before you type.

1. Row count

count() returns the number of rows in each group.

count() AS requests

2. Approximate distinct

uniq() estimates distinct counts fast; uniqExact() is the exact, slower variant.

uniq(user_id) AS approx_users

3. 95th percentile

quantile(0.95)(col) computes an approximate p95 over the group.

quantile(0.95)(latency) AS p95

4. Group key

GROUP BY collapses rows into one row per host.

GROUP BY host

A complete learning loop

Understand

Read the concept map and follow the code breakdown.

Reconstruct

Type the implementation, repair mistakes and work through every recall exercise.

Verify

Use the final check without hints; revisit weak concepts in short practice sessions.