How to Use CTEs in ClickHouse

How do I write and optimize a WITH (CTE) clause in ClickHouse?

A ClickHouse CTE (WITH clause) defines a temporary result set you can reference multiple times within a single query, improving readability and performance.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What problem does a ClickHouse CTE solve?

CTEs let you name a sub-query once and reuse it, avoiding repeated code and enabling step-by-step logic in complex reports.

How do you declare a CTE in ClickHouse?

Place a WITH clause before the main SELECT. Give each sub-query an alias and end the list with the final SELECT that uses those aliases.

Can I chain multiple CTEs?

Yes—separate every CTE with a comma. Each CTE can reference earlier ones, but not later ones.

Are recursive CTEs supported?

No. ClickHouse only supports non-recursive (single-pass) CTEs, so hierarchical data needs other techniques.

When should I use a CTE instead of a sub-query?

Use a CTE when the intermediate result appears more than once, when the logic benefits from naming, or when you want to simplify long SELECT statements for readability.

Example: compute customer lifetime value (CLV)

The example below calculates each customer’s total spend and average order value using two CTEs, then joins them for final output.

WITH customer_totals AS ( SELECT customer_id, SUM(total_amount) AS lifetime_spend FROM Orders GROUP BY customer_id ), order_stats AS ( SELECT customer_id, COUNT(*) AS orders_placed, AVG(total_amount) AS aov FROM Orders GROUP BY customer_id ) SELECT c.id, c.name, ct.lifetime_spend, os.orders_placed, os.aov FROM Customers AS c JOIN customer_totals AS ct ON ct.customer_id = c.id JOIN order_stats AS os ON os.customer_id = c.id ORDER BY ct.lifetime_spend DESC;.

Best practices for ClickHouse CTEs

Keep CTEs narrow—select only required columns. Give intuitive alias names. Profile execution plans: ClickHouse inlines CTEs, so performance is similar to sub-queries, but readability gains remain.

Common pitfalls

A missing comma between CTEs or omitting the final SELECT triggers syntax errors. Expecting recursive behavior also leads to confusion—ClickHouse stops at one evaluation pass.

Why How to Use CTEs in ClickHouse is important

How to Use CTEs in ClickHouse Example Usage


WITH high_value_customers AS (
    SELECT customer_id,
           SUM(total_amount) AS spend
    FROM Orders
    GROUP BY customer_id
    HAVING spend > 1000
),
recent_items AS (
    SELECT oi.product_id,
           COUNT(*) AS times_bought
    FROM OrderItems AS oi
    JOIN Orders      AS o ON o.id = oi.order_id
    WHERE o.order_date >= today() - 30
    GROUP BY oi.product_id
)
SELECT c.name,
       p.name  AS popular_product,
       p.price,
       hvc.spend
FROM Customers    AS c
JOIN high_value_customers AS hvc ON hvc.customer_id = c.id
CROSS JOIN recent_items         AS ri
JOIN Products                   AS p  ON p.id = ri.product_id
ORDER BY hvc.spend DESC, ri.times_bought DESC
LIMIT 10;

How to Use CTEs in ClickHouse Syntax


WITH
    cte_name AS (
        SELECT column_list
        FROM source_table
        [WHERE conditions]
        [GROUP BY columns]
    ),
    cte_name2 AS (
        SELECT ...
        FROM cte_name
        JOIN other_table USING (id)
    )
SELECT final_columns
FROM cte_name2
JOIN additional_table ON ...;

Common Mistakes

Forgetting the comma between multiple CTE definitions causes a syntax error. Always separate each CTE with a comma before the main SELECT.
Expecting recursive processing (e.g., hierarchical queries). ClickHouse evaluates each CTE only once; use arrays or pre-flattened tables instead.