CodeRang
SQL topics

SQL CTE: WITH and recursive queries

A SQL CTE defines a named intermediate result with WITH so later parts of the same statement can query it. The example below chains multiple CTEs and shows how a complex transformation can be split into readable steps.

Updated · SQL Arena team

CTE trackAll problems

What you will practice

  • Split a query into multiple CTEs
  • Feed one CTE into another
  • Replace deeply nested subqueries with named steps
  • Build recursive CTEs for hierarchies
  • Generate date and number sequences

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Find teams with a large payroll

The employees table contains salaries and team references, while teams contains team details. First calculate total payroll for each team, then use another CTE to keep teams with payroll of at least 200000. Return team_name and payroll, ordered from the highest payroll to the lowest.

Query 1PostgreSQLv16
WITH team_payroll AS (
    SELECT team_id, SUM(salary) AS payroll
    FROM employees GROUP BY team_id
), ranked AS (
    SELECT team_id, payroll FROM team_payroll WHERE payroll >= 200000
)
SELECT t.team_name, r.payroll
FROM ranked r JOIN teams t ON t.id = r.team_id
ORDER BY r.payroll DESC, t.team_name;
Submit
Resultteamsemployees
Answer accepted

How a SQL CTE works with WITH

The first CTE, team_payroll, has one job. It groups employees by team_id and calculates SUM(salary). At this stage, team names are unnecessary, so the intermediate result stays small: one team identifier and one payroll value per group.

The second CTE, ranked, reads from team_payroll instead of going back to the source table. It keeps only payroll values of at least 200000. This is the main reason multi-CTE queries are useful in practice: each step can consume the clean output of the previous one.

The final SELECT joins teams only when the human-readable team name is needed. The two-column ORDER BY makes the output deterministic. You could express the same logic with nested subqueries, but named CTEs become easier to inspect when a query grows into several transformations.

SQLPostgreSQL v16
WITH team_payroll AS (
    SELECT team_id, SUM(salary) AS payroll
    FROM employees GROUP BY team_id
), ranked AS (
    SELECT team_id, payroll FROM team_payroll WHERE payroll >= 200000
)
SELECT t.team_name, r.payroll
FROM ranked r JOIN teams t ON t.id = r.team_id
ORDER BY r.payroll DESC, t.team_name;
Query result
team_namepayroll
Delta370000
Atlas295000
Cipher225000

Common SQL CTE mistakes

01

Defining a CTE and then ignoring it

A WITH clause does not automatically change the final result. The main query, or another CTE, must actually read from it. A common mistake is to build the correct intermediate dataset and then accidentally query the original table again.

02

Filtering an aggregate too early

A condition based on SUM, COUNT, or another aggregate cannot be applied to individual source rows as if the aggregate already existed. Calculate the grouped value first, then filter it in another CTE or use HAVING.

03

Missing the stopping rule in recursion

A recursive CTE needs an anchor query and a recursive step that eventually stops producing new rows. Hierarchy problems require the correct parent-child relationship, while sequence problems need a clear upper date or numeric boundary.

Practice problems on this topic

CTE track

SQL Arena includes WITH clause exercises that go beyond memorizing syntax. Easier tasks let you isolate one calculation, such as an operating balance or the time from a KYC submission to approval. Medium problems add more transformations, ordering logic, and intermediate aggregates. The dedicated track “CTE: a query in multiple steps” provides a direct path from basic WITH queries to longer pipelines. SQL Arena contains 1,192 tasks, so each problem can introduce a different schema instead of repeating the same demo tables. Answers are checked against hidden data, which helps expose solutions that happen to work only for the visible rows.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What is a SQL CTE?

A SQL CTE, or common table expression, is a named query result declared with WITH. It can be referenced by the main query and, in suitable query structures, by later CTEs in the same statement.

How do you use a SQL CTE?

Write WITH followed by the CTE name and a query in parentheses, then reference that name in the following SQL statement. Multiple CTEs can be separated with commas.

What is the difference between a SQL CTE and a subquery?

Both can represent intermediate query logic, but a CTE gives that logic a name and places it before the main statement. This can make multi-step queries easier to read and organize.

Can a SQL CTE reference another CTE?

Yes. You can define several CTEs in one WITH clause, and a later CTE can use the result of an earlier one. This makes it possible to build a query as a sequence of named transformations.

What is a recursive SQL CTE?

A recursive CTE refers back to its own result and repeatedly applies its recursive part according to the query's termination logic. Recursive CTEs are commonly used for hierarchical data and generated sequences.

See also

Practice SQL CTE queries with exercises

Start with a single WITH clause, then move to chained CTEs and recursive problems. For a structured sequence, continue with the “CTE: a query in multiple steps” track.

Practice SQL CTEs

More practice from CodeRang