Common table expressions

Elena Dvoryadkina

Contents

CTE syntax
Examples
Recursive CTEs

Common table expression (CTE) is a named dataset that is defined within the scope of an SQL query, exists only during this query execution (CTE is not materialized and is not permanently stored anywhere in a database), and can be accessed multiple times within the main query.

CTE allows you to simplify and make a complex query more readable (you can divide the code into simple parts, reduce its size, avoid duplication), as well as to optimize the execution of a query that involves multiple access to the same datasets (the necessary data is temporarily stored in the cache so that it does not need to be searched for each time).

CTE syntax

To define CTE, use the WITH clause before the main (parent) query:

WITH <cte_name> AS (<cte_subquery>)
<primary_query>;

where:

<cte_name> — identifier by which CTE can be referenced in other parts of the main query;
<cte_subquery> — SQL query whose results populate CTE;
<primary_query> — main SQL query that uses CTE.

CTE syntax for using results of a scalar subquery is slightly different:

WITH (<cte_scalar_subquery>) AS <cte_name>
<primary_query>;

You can define multiple CTEs within a single WITH clause, separating them by commas:

WITH
    <cte1>,
    <cte2>,
    <cte3>
<primary_query>;

Each CTE can reference any previous one within the WITH clause.

Examples

This section provides examples of how CTE can be used in queries to analyze data about bonuses given to employees of a chain of stores in two cities in September. The data is stored in the employee_bonus_sept_24 table:

CREATE TABLE employee_bonus_sept_24 (
    empl_id UInt16,
    full_name String,
    position Enum('store manager' = 1, 'cashier' = 2, 'sales assistant' = 3),
    store_id UInt8,
    city Enum('New York' = 1, 'Chicago' = 2),
    bonus UInt32)
ENGINE = MergeTree
ORDER BY empl_id;

INSERT INTO employee_bonus_sept_24 VALUES
    (1, 'James Wilson', 1, 101, 1, 1000),
    (2, 'Mary Burton', 2, 101, 1, 450),
    (3, 'Patricia Robinson', 2, 101, 1, 500),
    (4, 'Robert Gray', 1, 102, 1, 1200),
    (5, 'Elizabeth Tucker', 2, 102, 1, 470),
    (6, 'Joseph Lewis', 3, 102, 1, 700),
    (7, 'William Ferguson', 1, 201, 2, 970),
    (8, 'Linda Black', 2, 201, 2, 350),
    (9, 'David Green', 2, 201, 2, 370),
    (10, 'Daniel Gray', 3, 201, 2, 770),
    (11, 'Mark Armstrong', 1, 202, 2, 1200),
    (12, 'Donald Carter', 2, 202, 2, 420),
    (13, 'Elizabeth Collins', 2, 202, 2, 400),
    (14, 'Paul Brown', 3, 202, 2, 660),
    (15, 'Andrew Clarke', 3, 202, 2, 680);

    ┌─empl_id─┬─full_name─────────┬─position────────┬─store_id─┬─city─────┬─bonus─┐
 1. │       1 │ James Wilson      │ store manager   │      101 │ New York │  1000 │
 2. │       2 │ Mary Burton       │ cashier         │      101 │ New York │   450 │
 3. │       3 │ Patricia Robinson │ cashier         │      101 │ New York │   500 │
 4. │       4 │ Robert Gray       │ store manager   │      102 │ New York │  1200 │
 5. │       5 │ Elizabeth Tucker  │ cashier         │      102 │ New York │   470 │
 6. │       6 │ Joseph Lewis      │ sales assistant │      102 │ New York │   700 │
 7. │       7 │ William Ferguson  │ store manager   │      201 │ Chicago  │   970 │
 8. │       8 │ Linda Black       │ cashier         │      201 │ Chicago  │   350 │
 9. │       9 │ David Green       │ cashier         │      201 │ Chicago  │   370 │
10. │      10 │ Daniel Gray       │ sales assistant │      201 │ Chicago  │   770 │
11. │      11 │ Mark Armstrong    │ store manager   │      202 │ Chicago  │  1200 │
12. │      12 │ Donald Carter     │ cashier         │      202 │ Chicago  │   420 │
13. │      13 │ Elizabeth Collins │ cashier         │      202 │ Chicago  │   400 │
14. │      14 │ Paul Brown        │ sales assistant │      202 │ Chicago  │   660 │
15. │      15 │ Andrew Clarke     │ sales assistant │      202 │ Chicago  │   680 │
    └─────────┴───────────────────┴─────────────────┴──────────┴──────────┴───────┘

Query with CTE

In the query below:

avg_bonus is CTE that calculates the average bonus for each position across all stores in a given month;
only those employees who received a bonus higher than the average are displayed.

WITH avg_bonus AS
    (
        SELECT position, round(avg(bonus)) AS position_avg_bonus
        FROM employee_bonus_sept_24
        GROUP BY position
    )
SELECT
    b.full_name AS employee,
    b.position,
    b.store_id AS store,
    b.bonus,
    ab.position_avg_bonus
FROM employee_bonus_sept_24 AS b
INNER JOIN avg_bonus AS ab ON b.position = ab.position
WHERE b.bonus > ab.position_avg_bonus
ORDER BY b.position, b.bonus DESC;

   ┌─employee──────────┬─position────────┬─store─┬─bonus─┬─position_avg_bonus─┐
1. │ Robert Gray       │ store manager   │   102 │  1200 │               1092 │
2. │ Mark Armstrong    │ store manager   │   202 │  1200 │               1092 │
3. │ Patricia Robinson │ cashier         │   101 │   500 │                423 │
4. │ Elizabeth Tucker  │ cashier         │   102 │   470 │                423 │
5. │ Mary Burton       │ cashier         │   101 │   450 │                423 │
6. │ Daniel Gray       │ sales assistant │   201 │   770 │                702 │
   └───────────────────┴─────────────────┴───────┴───────┴────────────────────┘

Window functions in CTE

If you need to filter query data by values of window functions, calculate these values within CTE (or a subquery).

For example, the following query:

in the bonus_rank_by_city CTE, ranks employee bonuses using the dense_rank() window function with windows defined by cities;
in the WHERE clause, filters rows by the calculated rank to return the 3 highest bonuses for employees in each city.

WITH bonus_rank_by_city AS
    (
        SELECT *, dense_rank() OVER (PARTITION BY city ORDER BY bonus DESC) AS rnk
        FROM employee_bonus_sept_24
        ORDER BY city, rnk
    )
SELECT *
FROM bonus_rank_by_city
WHERE rnk BETWEEN 1 AND 3;

   ┌─empl_id─┬─full_name────────┬─position────────┬─store_id─┬─city─────┬─bonus─┬─rnk─┐
1. │       4 │ Robert Gray      │ store manager   │      102 │ New York │  1200 │   1 │
2. │       1 │ James Wilson     │ store manager   │      101 │ New York │  1000 │   2 │
3. │       6 │ Joseph Lewis     │ sales assistant │      102 │ New York │   700 │   3 │
4. │      11 │ Mark Armstrong   │ store manager   │      202 │ Chicago  │  1200 │   1 │
5. │       7 │ William Ferguson │ store manager   │      201 │ Chicago  │   970 │   2 │
6. │      10 │ Daniel Gray      │ sales assistant │      201 │ Chicago  │   770 │   3 │
   └─────────┴──────────────────┴─────────────────┴──────────┴──────────┴───────┴─────┘

Multiple CTEs in a query

The following query uses two CTEs:

avg_bonus_position — average monthly bonus for a position;
avg_bonus_city — average monthly bonus for a city.

WITH
    avg_bonus_position AS
    (
        SELECT position, round(avg(bonus)) AS position_avg_bonus
        FROM employee_bonus_sept_24
        GROUP BY position
    ),
    avg_bonus_city AS
    (
        SELECT city, round(avg(bonus)) AS city_avg_bonus
        FROM employee_bonus_sept_24
        GROUP BY city
    )
SELECT
    b.full_name AS employee,
    b.position AS position,
    b.store_id AS store,
    b.city AS city,
    b.bonus,
    abp.position_avg_bonus,
    abc.city_avg_bonus
FROM employee_bonus_sept_24 AS b
INNER JOIN avg_bonus_position AS abp ON b.position = abp.position
INNER JOIN avg_bonus_city AS abc ON b.city = abc.city
ORDER BY b.position, b.bonus DESC;

    ┌─employee──────────┬─position────────┬─store─┬─city─────┬─bonus─┬─position_avg_bonus─┬─city_avg_bonus─┐
 1. │ Robert Gray       │ store manager   │   102 │ New York │  1200 │               1092 │            720 │
 2. │ Mark Armstrong    │ store manager   │   202 │ Chicago  │  1200 │               1092 │            647 │
 3. │ James Wilson      │ store manager   │   101 │ New York │  1000 │               1092 │            720 │
 4. │ William Ferguson  │ store manager   │   201 │ Chicago  │   970 │               1092 │            647 │
 5. │ Patricia Robinson │ cashier         │   101 │ New York │   500 │                423 │            720 │
 6. │ Elizabeth Tucker  │ cashier         │   102 │ New York │   470 │                423 │            720 │
 7. │ Mary Burton       │ cashier         │   101 │ New York │   450 │                423 │            720 │
 8. │ Donald Carter     │ cashier         │   202 │ Chicago  │   420 │                423 │            647 │
 9. │ Elizabeth Collins │ cashier         │   202 │ Chicago  │   400 │                423 │            647 │
10. │ David Green       │ cashier         │   201 │ Chicago  │   370 │                423 │            647 │
11. │ Linda Black       │ cashier         │   201 │ Chicago  │   350 │                423 │            647 │
12. │ Daniel Gray       │ sales assistant │   201 │ Chicago  │   770 │                702 │            647 │
13. │ Joseph Lewis      │ sales assistant │   102 │ New York │   700 │                702 │            720 │
14. │ Andrew Clarke     │ sales assistant │   202 │ Chicago  │   680 │                702 │            647 │
15. │ Paul Brown        │ sales assistant │   202 │ Chicago  │   660 │                702 │            647 │
    └───────────────────┴─────────────────┴───────┴──────────┴───────┴────────────────────┴────────────────┘

Nested CTEs

The following query allows you to compare the amount of funds allocated for employee bonuses in different stores. It shows what percentage the amount of bonuses paid to employees in each store takes up from the total amount of expenses on bonuses for employees of the entire chain. To do this, the query uses nested CTEs:

bonus_all_stores — total amount of expenses on bonuses for employees of all stores;
store_bonus_totals_and_rates — the amount of bonuses paid to employees in each store and its share relative to the total employee bonus expense in the month, calculated using the output of the bonus_all_stores CTE.

WITH
    (SELECT sum(bonus) AS all_stores_bonus FROM employee_bonus_sept_24) AS bonus_all_stores,
    store_bonus_totals_and_rates AS
    (
        SELECT
            store_id,
            count() AS employee_count,
            sum(bonus) AS store_bonus_total,
            store_bonus_total / bonus_all_stores AS store_bonus_rate
        FROM employee_bonus_sept_24
        GROUP BY store_id
    )
SELECT
    store_id,
    employee_count,
    store_bonus_total AS total_bonus,
    round(store_bonus_rate * 100, 2) AS bonus_percentage
FROM store_bonus_totals_and_rates;

   ┌─store_id─┬─employee_count─┬─total_bonus─┬─bonus_percentage─┐
1. │      101 │              3 │        1950 │            19.23 │
2. │      102 │              3 │        2370 │            23.37 │
3. │      201 │              4 │        2460 │            24.26 │
4. │      202 │              5 │        3360 │            33.14 │
   └──────────┴────────────────┴─────────────┴──────────────────┘

Recursive CTEs

Overview

ADQM/ClickHouse supports recursive CTEs that you can use to work with hierarchical or tree-structured data. This type of CTE allows a query to be executed repeatedly, each time referring to its own results obtained at the previous iteration.

To create a recursive CTE, use the RECURSIVE modifier after WITH. The basic syntax of a recursive CTE is:

WITH RECURSIVE <cte_name> AS (
    <cte_non_recursive_term>
    UNION ALL
    <cte_recursive_term>
)
SELECT <list_of_columns> FROM <cte_name>;

where:

<cte_non_recursive_term> — non-recursive term of the CTE query that is executed first. Received data is written to the result set of the CTE and is also used as input values for the first call of the recursive term.
<cte_recursive_term> — recursive term of the CTE query that refers to the result of its execution at the previous iteration (available under the CTE name — <cte_name>) and is executed until it returns any rows. The result of the recursive term, obtained at each iteration, is also written to the CTE’s result set that will be available in the main query (under the same name — <cte_name>) after the entire CTE query has been processed.

For example, create a table to store information about positions in a company:

CREATE TABLE positions (
    id UInt32,
    position String,
    manager_id Nullable(UInt32)
) ENGINE = Memory;

Fill in the table with test data:

INSERT INTO positions VALUES
    (1, 'CEO', NULL),
    (2, 'CTO', 1),
    (3, 'Backend Team Lead', 2),
    (4, 'Frontend Team Lead', 2),
    (5, 'Backend Developer Senior', 3),
    (6, 'Backend Developer Middle', 3),
    (7, 'Frontend Developer Senior', 4),
    (8, 'Frontend Developer Middle', 4),
    (9, 'Frontend Developer Junior', 4),
    (10, 'UX/UI Designer', 2);

Run a query with a recursive CTE to see who employees at different positions in the company’s organizational structure report to:

WITH RECURSIVE employee_hierarchy AS (
    -- Non-recursive term: start with CEO
    SELECT id, position, manager_id, toUInt16(0) AS level, [position] AS path
    FROM positions
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive term: add subordinates
    SELECT p.id, p.position, p.manager_id, eh.level + 1, arrayConcat(eh.path, [p.position]) AS path
    FROM positions p
    JOIN employee_hierarchy eh ON p.manager_id = eh.id
)
SELECT position, level, arrayStringConcat(path, ' -> ') AS full_path
FROM employee_hierarchy;

This query is executed in the following order:

The CTE’s non-recursive term returns the initial data from the positions table — a position for which no manager is specified (by the manager_id IS NULL condition). In this example, it is CEO — the top position in the company hierarchy, which is assigned a level of 0.
The output from the non-recursive term is used to evaluate the first iteration of the CTE’s recursive term, which finds positions that report to CEO by joining the positions table and the employee_hierarchy CTE with the positions.manager_id = employee_hierarchy.id join key. Thus, the first iteration of the recursive term returns the CTO position (for which the manager_id value is equal to the CEO position’s identifier) and assigns it a level of 1 in the company hierarchy. The output of this iteration is added to the CTE’s result set and serves as input for the next iteration.
Two more iterations of the recursive term are performed and return the positions of the next two levels in the company hierarchy — 2 and 3.
The fourth iteration returns an empty result, as the third-level positions in the positions table have no subordinates — the CTE execution ends here.
The main query calls the CTE to select data from its final result set, which includes the results of the non-recursive term and all iterations of the recursive term.

   ┌─position─┬─level─┬─full_path─┐
1. │ CEO      │     0 │ CEO       │
   └──────────┴───────┴───────────┘
   ┌─position─┬─level─┬─full_path──┐
2. │ CTO      │     1 │ CEO -> CTO │
   └──────────┴───────┴────────────┘
   ┌─position───────────┬─level─┬─full_path────────────────────────┐
3. │ Backend Team Lead  │     2 │ CEO -> CTO -> Backend Team Lead  │
4. │ Frontend Team Lead │     2 │ CEO -> CTO -> Frontend Team Lead │
5. │ UX/UI Designer     │     2 │ CEO -> CTO -> UX/UI Designer     │
   └────────────────────┴───────┴──────────────────────────────────┘
    ┌─position──────────────────┬─level─┬─full_path─────────────────────────────────────────────────────┐
 6. │ Backend Developer Senior  │     3 │ CEO -> CTO -> Backend Team Lead -> Backend Developer Senior   │
 7. │ Backend Developer Middle  │     3 │ CEO -> CTO -> Backend Team Lead -> Backend Developer Middle   │
 8. │ Frontend Developer Senior │     3 │ CEO -> CTO -> Frontend Team Lead -> Frontend Developer Senior │
 9. │ Frontend Developer Middle │     3 │ CEO -> CTO -> Frontend Team Lead -> Frontend Developer Middle │
10. │ Frontend Developer Junior │     3 │ CEO -> CTO -> Frontend Team Lead -> Frontend Developer Junior │
    └───────────────────────────┴───────┴───────────────────────────────────────────────────────────────┘

Search order

The above query outputs data in the order it was added to the recursive CTE’s result set — sorted in the breadth-first order by the nesting level of the recursive query, which corresponds to the level of a position in the company hierarchy (level) in this case.

To sort the results in the depth-first order, apply sorting by the path field that stores the path to each row as an array of already-visited rows (in this example, this is a sequence of all higher positions in the company hierarchy for each position).

WITH RECURSIVE employee_hierarchy AS (
    -- Non-recursive term: start with CEO
    SELECT id, position, manager_id, toUInt16(0) AS level, [position] AS path
    FROM positions
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive term: add subordinates
    SELECT p.id, p.position, p.manager_id, eh.level + 1, arrayConcat(eh.path, [p.position]) AS path
    FROM positions p
    JOIN employee_hierarchy eh ON p.manager_id = eh.id
)
SELECT repeat('    ', level) || position AS position, level, arrayStringConcat(path, ' -> ') AS full_path
FROM employee_hierarchy
ORDER BY path;

    ┌─position──────────────────────────────┬─level─┬─full_path─────────────────────────────────────────────────────┐
 1. │ CEO                                   │     0 │ CEO                                                           │
 2. │     CTO                               │     1 │ CEO -> CTO                                                    │
 3. │         Backend Team Lead             │     2 │ CEO -> CTO -> Backend Team Lead                               │
 4. │             Backend Developer Middle  │     3 │ CEO -> CTO -> Backend Team Lead -> Backend Developer Middle   │
 5. │             Backend Developer Senior  │     3 │ CEO -> CTO -> Backend Team Lead -> Backend Developer Senior   │
 6. │         Frontend Team Lead            │     2 │ CEO -> CTO -> Frontend Team Lead                              │
 7. │             Frontend Developer Junior │     3 │ CEO -> CTO -> Frontend Team Lead -> Frontend Developer Junior │
 8. │             Frontend Developer Middle │     3 │ CEO -> CTO -> Frontend Team Lead -> Frontend Developer Middle │
 9. │             Frontend Developer Senior │     3 │ CEO -> CTO -> Frontend Team Lead -> Frontend Developer Senior │
10. │         UX/UI Designer                │     2 │ CEO -> CTO -> UX/UI Designer                                  │
    └───────────────────────────────────────┴───────┴───────────────────────────────────────────────────────────────┘

Cycle detection

When working with a recursive CTE, it is important to check whether the recursive part of the query does not lead to an infinite loop and, if necessary, set conditions to stop recursion. This section provides examples of various mechanisms to prevent loops in queries:

Track already-processed values
Limit recursion depth
Use LIMIT
Limit by value

Track already-processed values

The standard way to detect cycles is to track one or more fields and check whether the current value has been processed before (in other words, whether the current point has already been reached by executing a previous sequence of recursions).

The following example shows how to traverse a graph with a cycle, avoiding infinite recursion.

Create the graph table:

CREATE TABLE graph (
    from_node UInt32,
    to_node UInt32
) ENGINE = MergeTree()
ORDER BY (from_node, to_node);

Fill the table with test data simulating a graph with some cycle (for example, 1 → 2 → 4 → 7 → 1):

INSERT INTO graph VALUES
    (1, 2), (1, 3),
    (2, 4), (2, 5),
    (3, 6),
    (4, 7), (4, 8),
    (5, 9),
    (7, 1),
    (6, 10);

A graph traversal query without cycle detection will loop indefinitely and fail with the exception Maximum recursive CTE evaluation depth (1000) exceeded:

WITH RECURSIVE graph_traversal AS (
    SELECT from_node, to_node,
    FROM graph
    WHERE from_node = 1 -- Start with the node 1

    UNION ALL

    SELECT g.from_node, g.to_node,
    FROM graph_traversal AS gt
    JOIN graph AS g ON gt.to_node = g.from_node
)
SELECT from_node, to_node,
FROM graph_traversal;

To avoid looping the query, save the traversed paths to the graph nodes as arrays of already-visited nodes in the path field and use them to track the re-traversal of nodes, writing the cycle detection flag in the has_cycle field:

WITH RECURSIVE graph_traversal AS (
    SELECT from_node, to_node, 1 AS depth, [from_node, to_node] AS path, false AS has_cycle
    FROM graph
    WHERE from_node = 1 -- Start with the node 1

    UNION ALL

    SELECT g.from_node, g.to_node, gt.depth + 1, arrayPushBack(gt.path, g.to_node), has(gt.path, g.to_node) AS has_cycle
    FROM graph_traversal AS gt
    JOIN graph AS g ON gt.to_node = g.from_node
    WHERE NOT gt.has_cycle -- Stop recursion when a cycle is detected
)
SELECT from_node, to_node, depth, path, has_cycle, arrayStringConcat(path, ' -> ') AS path_str
FROM graph_traversal;

Query result

   ┌─from_node─┬─to_node─┬─depth─┬─path──┬─has_cycle─┬─path_str─┐
1. │         1 │       2 │     1 │ [1,2] │ false     │ 1 -> 2   │
2. │         1 │       3 │     1 │ [1,3] │ false     │ 1 -> 3   │
   └───────────┴─────────┴───────┴───────┴───────────┴──────────┘
   ┌─from_node─┬─to_node─┬─depth─┬─path────┬─has_cycle─┬─path_str────┐
3. │         2 │       4 │     2 │ [1,2,4] │ false     │ 1 -> 2 -> 4 │
4. │         2 │       5 │     2 │ [1,2,5] │ false     │ 1 -> 2 -> 5 │
5. │         3 │       6 │     2 │ [1,3,6] │ false     │ 1 -> 3 -> 6 │
   └───────────┴─────────┴───────┴─────────┴───────────┴─────────────┘
   ┌─from_node─┬─to_node─┬─depth─┬─path───────┬─has_cycle─┬─path_str──────────┐
6. │         4 │       7 │     3 │ [1,2,4,7]  │ false     │ 1 -> 2 -> 4 -> 7  │
7. │         4 │       8 │     3 │ [1,2,4,8]  │ false     │ 1 -> 2 -> 4 -> 8  │
8. │         5 │       9 │     3 │ [1,2,5,9]  │ false     │ 1 -> 2 -> 5 -> 9  │
9. │         6 │      10 │     3 │ [1,3,6,10] │ false     │ 1 -> 3 -> 6 -> 10 │
   └───────────┴─────────┴───────┴────────────┴───────────┴───────────────────┘
    ┌─from_node─┬─to_node─┬─depth─┬─path────────┬─has_cycle─┬─path_str──────────────┐
10. │         7 │       1 │     4 │ [1,2,4,7,1] │ true      │ 1 -> 2 -> 4 -> 7 -> 1 │
    └───────────┴─────────┴───────┴─────────────┴───────────┴───────────────────────┘

Limit recursion depth

When executing a recursive CTE, you can store the nesting level of the recursions in a separate column and use it to limit the depth of the search. In the example below, this is the depth column:

WITH RECURSIVE graph_traversal AS (
    SELECT from_node, to_node, 1 AS depth, [from_node, to_node] AS path
    FROM graph
    WHERE from_node = 1 -- Start with the node 1

    UNION ALL

    SELECT g.from_node, g.to_node, gt.depth + 1, arrayPushBack(gt.path, g.to_node)
    FROM graph_traversal AS gt
    JOIN graph AS g ON gt.to_node = g.from_node
    WHERE gt.depth < 3 -- Limiting the depth of recursion
)
SELECT from_node, to_node, depth, path, arrayStringConcat(path, ' -> ') AS path_str
FROM graph_traversal;

You can also configure the maximum depth of recursive CTEs in ADQM at the session level using the max_recursive_cte_evaluation_depth parameter (the default value is 1000), for example:

SET max_recursive_cte_evaluation_depth = 3;

Use LIMIT

Another way to stop the infinite recursion of a CTE is to add LIMIT to the main query. For example, the following query calculates the factorial of numbers from 1 to 10:

WITH RECURSIVE r AS (
    SELECT 1 AS i, toUInt32(1) AS factorial
    UNION ALL
    SELECT i+1 AS i, factorial*i as factorial
    FROM r
)
SELECT * FROM r LIMIT 10;

Query result

   ┌─i─┬─factorial─┐
1. │ 1 │         1 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
2. │ 2 │         2 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
3. │ 3 │         6 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
4. │ 4 │        24 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
5. │ 5 │       120 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
6. │ 6 │       720 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
7. │ 7 │      5040 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
8. │ 8 │     40320 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
9. │ 9 │    362880 │
   └───┴───────────┘
    ┌──i─┬─factorial─┐
10. │ 10 │   3628800 │
    └────┴───────────┘

NOTE

In this example, the 1 initial value in the factorial field is cast to the UInt32 type to avoid data type overflow during the factorial calculation, since the default type is UInt8.

Limit by value

You can also use a value of some field as a condition for stopping recursion. For example:

WITH RECURSIVE r AS (
    SELECT 1 AS i, toUInt32(1) AS factorial
    UNION ALL
    SELECT i+1 AS i, factorial*i as factorial
    FROM r
    WHERE factorial < 5000
)
SELECT * FROM r;

Query result

   ┌─i─┬─factorial─┐
1. │ 1 │         1 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
2. │ 2 │         2 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
3. │ 3 │         6 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
4. │ 4 │        24 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
5. │ 5 │       120 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
6. │ 6 │       720 │
   └───┴───────────┘

Found a mistake? Seleсt text and press Ctrl+Enter to report it