I85PREP
⌘ K
ClickHouse Certified study materials
ClickHouse Certified study resources

ClickHouse Certified: Querying with SQL

ClickHouse Certified · Work through your exam, one topic at a time.

All guide sections

Querying with SQL

This domain tests ClickHouse's SQL dialect: filtering stages, aggregation and window idioms, join strictness and direction, cluster-aware constructs, and per-query settings. Expect questions that hand you a query and ask why it is slow, wrong, or non-deterministic.

Filtering and reading stage

  • PREWHERE applies a cheap filter before reading the remaining columns; optimize_move_to_prewhere does this automatically, so hand-written PREWHERE is a tuning tool, not a default.
  • FINAL forces merge-on-read for Replacing/Collapsing tables - correct but expensive on wide scans.
  • SAMPLE k requires the table to declare SAMPLE BY.

Aggregation idioms

  • uniq() is approximate (HyperLogLog) and memory-bounded; uniqExact() and count(DISTINCT x) are exact and costly at high cardinality.
  • argMax(col, ts), groupArray, quantile(0.5)/median, topK cover most "latest value" and percentile questions.
  • LIMIT n BY col returns the first n rows per group after ORDER BY - cheaper than a window function for top-N-per-group.
  • WITH TOTALS, ROLLUP, CUBE, and standard OVER (PARTITION BY ... ORDER BY ...) windows are supported.

Joins

  • Default join_algorithm = hash builds the hash table from the right-hand table, so the smaller table belongs on the right - the opposite of many engines.
  • join_use_nulls = 0 by default: unmatched left rows receive type defaults, not NULL. Set join_use_nulls = 1 for standard SQL semantics.
  • Strictness ALL is the default; ANY, SEMI/ANTI, and ASOF (time-series nearest-match) are available.
  • On a cluster, GLOBAL JOIN / GLOBAL IN ship the right side once instead of rebuilding it per shard.

Worked example

Top 3 event types per user: SELECT user_id, event, c FROM (SELECT user_id, event, count() AS c FROM events GROUP BY user_id, event) ORDER BY c DESC LIMIT 3 BY user_id The inner aggregate reduces data first; LIMIT 3 BY then keeps the first three rows per user_id in the sorted stream. A window-function rewrite scans the same rows but materializes more state.

Settings and output

  • Prefer the SETTINGS max_execution_time = 10 clause over session-level SET, which leaks into later queries on the same connection.
  • Always specify FORMAT when piping results into tooling; verify index usage with EXPLAIN indexes = 1.

Traps that cost marks

  • SELECT * on wide tables and NOT IN against Nullable columns (NULL makes the predicate never true).
  • Assuming unmatched joins yield NULL when join_use_nulls is off.
  • Using count(DISTINCT) on millions of distinct values.