SQL Queries & Aggregations

ClickHouse speaks SQL — but with superpowers. Learn to write queries that crunch billions of rows in milliseconds.

📊 Basic SELECT

ClickHouse SQL is 95% standard SQL. If you know PostgreSQL or MySQL, you already know most of it.

-- Basic queries work exactly as expected
SELECT * FROM events LIMIT 10;

SELECT user_id, event_type, count() AS cnt
FROM events
WHERE event_date >= '2024-01-01'
GROUP BY user_id, event_type
ORDER BY cnt DESC
LIMIT 20;

💡 ClickHouse Shortcut: count()

In ClickHouse, count() (without arguments) is the same as COUNT(*) but slightly faster. Use it!

🔢 Aggregation Functions

ClickHouse has 100+ built-in aggregation functions. Here are the most important ones:

SELECT
    count()                          AS total_rows,
    uniq(user_id)                    AS unique_users,
    sum(revenue)                     AS total_revenue,
    avg(response_time)               AS avg_response,
    min(response_time)               AS min_response,
    max(response_time)               AS max_response,
    quantile(0.50)(response_time)    AS median,
    quantile(0.95)(response_time)    AS p95,
    quantile(0.99)(response_time)    AS p99,
    topK(5)(url)                     AS top_5_urls,
    groupArray(10)(event_type)      AS sample_events
FROM events
WHERE event_date = today();

🧒 ELI5: What are Quantiles?

Imagine 100 students take a test. Quantile(0.50) (median) = the score of the 50th student when sorted. Quantile(0.95) (p95) = the score of the 95th student. p95 tells you "95% of users had a response time BELOW this value." It's the industry standard for measuring performance.

Special ClickHouse Functions

Function	What It Does	PostgreSQL Equivalent
`uniq()`	Approximate COUNT DISTINCT (fast!)	`COUNT(DISTINCT x)`
`uniqExact()`	Exact COUNT DISTINCT	`COUNT(DISTINCT x)`
`topK(N)(col)`	Top N most frequent values	No equivalent
`quantile(p)(col)`	Percentile value	`percentile_cont(p)`
`groupArray(col)`	Collect values into array	`array_agg(col)`
`argMax(a, b)`	Value of a where b is maximum	Subquery needed

📅 Time-Series Queries

ClickHouse excels at time-series analytics. Here are common patterns:

-- Hourly traffic breakdown
SELECT
    toStartOfHour(event_time) AS hour,
    count() AS events,
    uniq(user_id) AS unique_users
FROM events
WHERE event_date = today()
GROUP BY hour
ORDER BY hour;

-- 7-day rolling average
SELECT
    event_date,
    count() AS daily_events,
    avg(count()) OVER (
        ORDER BY event_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d_avg
FROM events
GROUP BY event_date
ORDER BY event_date;

🌍 Real World: Dashboard Query

This is exactly the kind of query that powers real-time dashboards at companies like Uber and Cloudflare. ClickHouse can return these results for billions of rows in under 100ms.

🔗 JOINs in ClickHouse

⚠️ JOINs Are Expensive!

ClickHouse can do JOINs, but they're much slower than in PostgreSQL. The right table is loaded entirely into memory. Always put the smaller table on the RIGHT side of the JOIN.

-- Small dimension table on the RIGHT
SELECT
    e.event_type,
    d.category_name,
    count() AS cnt
FROM events AS e                  -- big table (LEFT)
JOIN dim_categories AS d          -- small table (RIGHT)
    ON e.category_id = d.id
GROUP BY e.event_type, d.category_name;

💡 Better Alternative: Dictionary

For small lookup tables, use ClickHouse Dictionaries instead of JOINs. They're cached in memory and accessed via dictGet() — much faster than a JOIN!

SELECT
    event_type,
    dictGet('categories', 'name', category_id) AS category_name,
    count() AS cnt
FROM events
GROUP BY event_type, category_name;

🧪 Hands-On Exercise

Try this yourself! Create a table and run analytics:

-- 1. Create table
CREATE TABLE web_logs (
    ts DateTime,
    url String,
    status_code UInt16,
    response_ms UInt32,
    country LowCardinality(String)
) ENGINE = MergeTree() ORDER BY ts;

-- 2. Insert 10M rows
INSERT INTO web_logs
SELECT
    now() - toIntervalSecond(rand() % 604800),
    arrayElement(['/api/users','/api/orders','/home','/login'], (rand()%4)+1),
    arrayElement([200,201,404,500], (rand()%4)+1),
    rand() % 3000,
    arrayElement(['US','UK','IN','DE','JP','BR'], (rand()%6)+1)
FROM numbers(10000000);

-- 3. Now try these queries:
-- a) Top 5 countries by error rate (status >= 400)
-- b) Hourly p95 response time for /api/users
-- c) Which URL has the most 500 errors?

Data Types Advanced SQL

October 2026 practice

Checked against the ClickHouse documentation in October 2026. The JSON type has been production-ready since version 25.3. ClickHouse Cloud rewrites a MergeTree create onto the matching SharedMergeTree engine.

Filter on columns that match the ORDER BY prefix so the sparse index can skip granules. Aggregate the columns you need. A JOIN of two huge unsored tables will spill. PREWHERE still helps when a selective condition can drop granules before the rest of the row is read. Avoid SELECT * on a wide table.

SELECT user_id, count()
FROM events
WHERE day = '2026-10-13'
GROUP BY user_id