clickhouse.guru
Tutorials· beginner· Tested on ClickHouse 26.4

Common table expressions (WITH) in ClickHouse

WITH in ClickHouse names constants, expressions, scalar subqueries and whole queries. How to refactor a nested query into CTEs, and what CTEs do and do not do for performance.

A common table expression (CTE) gives a name to something at the top of a query so you can use it below. In ClickHouse the WITH clause is broader than in most databases: the name can stand for a whole subquery, but also for a constant, an expression or a scalar subquery.

The two forms

WITH <name> AS (<query>)          -- name for a query (a table-like thing)
WITH <expression> AS <name>       -- name for an expression or value

Note the opposite order: for a query the name comes first, for an expression the name comes last.

WITH
    my_numbers AS (SELECT number FROM numbers(100)),   -- a query
    42 AS target,                                      -- a constant
    now() AS this_moment                               -- an expression
SELECT number, target
FROM my_numbers
WHERE number = target;
42	42

You can mix both kinds in one WITH, and a later entry can refer to an earlier one. The reverse does not work: an entry cannot refer to one defined after it, so circular definitions fail.

WITH v1 AS (SELECT * FROM v2), v2 AS (SELECT * FROM v1) SELECT * FROM v1;
-- Code: 60. Unknown table expression identifier 'v1' in scope v2. (UNKNOWN_TABLE)

A CTE lives only inside the query that defines it.

Worked example: refactoring a nested query

Take the demo events table:

CREATE TABLE events (event_time DateTime, user_id UInt64, event_type LowCardinality(String),
                     url String, duration_ms UInt32, country LowCardinality(String))
ENGINE = MergeTree ORDER BY (event_type, event_time);

INSERT INTO events
SELECT toDateTime('2026-03-01 00:00:00') + intDiv(number, 3) * 60, number % 50,
       ['view','click','purchase'][1 + number % 3], concat('/p/', toString(number % 7)),
       10 + (number * 7) % 500, ['DE','TR','US'][1 + intDiv(number, 3) % 3]
FROM numbers(3000);

The task: compare the hourly average duration of view events in two countries over the first six hours. The first draft is the usual nested query with everything repeated:

SELECT h, avg_a, avg_b FROM (
    SELECT toStartOfHour(event_time) AS h, avg(duration_ms) AS avg_a
    FROM events
    WHERE event_time BETWEEN '2026-03-01 00:00:00' AND toDateTime('2026-03-01 00:00:00') + INTERVAL 6 HOUR
      AND country = 'DE' AND event_type = 'view'
    GROUP BY h
) AS a
INNER JOIN (
    SELECT toStartOfHour(event_time) AS h, avg(duration_ms) AS avg_b
    FROM events
    WHERE event_time BETWEEN '2026-03-01 00:00:00' AND toDateTime('2026-03-01 00:00:00') + INTERVAL 6 HOUR
      AND country = 'TR' AND event_type = 'view'
    GROUP BY h
) AS b USING (h);

Problems: the time filter and the event type are copied into both subqueries, so changing one and forgetting the other silently gives a wrong answer; and every literal you want to tweak is buried in the middle of the text.

Refactor in small steps, each one runnable on its own:

  1. Move the innermost subqueries into the WITH section and give them names (a, b).
  2. Replace repeated literals with named constants (country_a, start_ts, …).
  3. Derive values from other values (end_ts from start_ts and hours).
WITH
    -- parameters
    'DE' AS country_a,
    'TR' AS country_b,
    toDateTime('2026-03-01 00:00:00') AS start_ts,
    6 AS hours,
    -- derived
    start_ts + INTERVAL hours HOUR AS end_ts,
    a AS (SELECT toStartOfHour(event_time) AS h, avg(duration_ms) AS avg_a
          FROM events
          WHERE event_time BETWEEN start_ts AND end_ts
            AND country = country_a AND event_type = 'view'
          GROUP BY h),
    b AS (SELECT toStartOfHour(event_time) AS h, avg(duration_ms) AS avg_b
          FROM events
          WHERE event_time BETWEEN start_ts AND end_ts
            AND country = country_b AND event_type = 'view'
          GROUP BY h)
SELECT h, round(avg_a, 1) AS avg_a, round(avg_b, 1) AS avg_b
FROM a INNER JOIN b USING (h)
ORDER BY h;
┌───────────────────h─┬─avg_a─┬─avg_b─┐
│ 2026-03-01 00:00:00 │ 208.5 │ 229.5 │
│ 2026-03-01 01:00:00 │ 268.5 │ 289.5 │
│ 2026-03-01 02:00:00 │ 228.5 │ 249.5 │
│ 2026-03-01 03:00:00 │ 288.5 │ 309.5 │
│ 2026-03-01 04:00:00 │ 248.5 │ 219.5 │
│ 2026-03-01 05:00:00 │ 308.5 │ 254.5 │
└─────────────────────┴───────┴───────┘

Now everything you would want to change is in the first six lines, and the final SELECT reads like the question you asked. This is the whole point of CTEs: readability and one place per fact. If a and b only differ by country, the next step would be a single query with GROUP BY country, h, but the refactoring above works for any shape.

Scalar subqueries as named values

A subquery that returns one row and one column can be named with the expression form. It is evaluated once and used as a constant:

WITH (SELECT max(duration_ms) FROM events) AS slowest
SELECT event_type, countIf(duration_ms >= slowest * 0.9) AS near_slowest
FROM events GROUP BY event_type ORDER BY event_type;

This is also the clean way to write the “look up an ID by name” pattern: (SELECT id FROM dim WHERE name = 'x') AS the_id, then WHERE id = the_id.

Chaining CTEs

WITH per_user AS (SELECT user_id, count() AS n FROM events GROUP BY user_id),
     stats    AS (SELECT avg(n) AS mean_n FROM per_user)
SELECT countIf(n > mean_n) AS heavy_users, count() AS users
FROM per_user, stats;

stats reads from per_user; the main query reads from both.

Recursive CTEs

Recent versions support WITH RECURSIVE, which is handy for generating sequences or walking a hierarchy:

WITH RECURSIVE days AS (
    SELECT toDate('2026-03-01') AS d
    UNION ALL
    SELECT d + 1 FROM days WHERE d < toDate('2026-03-05')
)
SELECT d FROM days;
2026-03-01
2026-03-02
2026-03-03
2026-03-04
2026-03-05

The recursion depth is capped by the max_recursive_cte_evaluation_depth setting. For plain date or number series, numbers() is usually simpler and faster.

Performance: a CTE is not a cache

It is easy to assume that a CTE is computed once and reused. By default it is not: a named query is substituted wherever it is referenced, like a view, so referring to it twice runs it twice. You can see this with a non-deterministic function; each reference gets its own evaluation:

WITH x AS (SELECT number, rand() AS v FROM numbers(3))
SELECT countIf(a.v = b.v) FROM x AS a JOIN x AS b ON a.number = b.number;
-- 0: the two sides saw different random values

Newer versions can materialize a CTE so it is computed once. It is opt-in, with the MATERIALIZED keyword plus a setting:

WITH x AS MATERIALIZED (SELECT number, rand() AS v FROM numbers(3))
SELECT countIf(a.v = b.v) FROM x AS a JOIN x AS b ON a.number = b.number
SETTINGS enable_materialized_cte = 1;
-- 3

The setting is off by default and the feature is young, so test it on your version before relying on it. Scalar CTEs (the expression form) are evaluated once as constants. If an expensive intermediate result is used many times, the dependable options are a temporary table, or a real materialized view.

Gotcha: distributed tables

Older notes report that nested CTEs combined with IN on Distributed tables can misbehave, because the CTE is textually inlined into the query sent to the shards. If a query with CTEs gives odd results on a Distributed table, test the same query with the CTE replaced by an inline subquery, and check the behaviour on your version (this one could not be reproduced without a cluster).

Reference: WITH clause in the docs.

sqlcte

Discussion

Comments live in GitHub Discussions. Sign in with GitHub to join in.