CTEs & Recursive Queries

WITH for readable step-by-step queries, WITH RECURSIVE for hierarchies, and generate_series.

A common table expression (WITH name AS (...)) gives a subquery a name, so a complex query can be written as a series of readable steps. WITH RECURSIVE goes further: a query that refers to its own output, which walks trees and graphs such as reporting lines, bills of materials or network paths.

Steps, hierarchies and series

project_hours is computed once and then joined; sum(...) OVER () gives the total over all rows for the percentage.

The recursive CTE starts with Rahul Verma (the anchor), then repeatedly adds employees whose manager is already in the result.

path accumulates the chain of names, so the hierarchy can be printed in order.

generate_series produces a sequence of values — here every Monday between two dates.

How recursion runs

WITH RECURSIVE r AS (... UNION ALL SELECT ... FROM r JOIN edges ...) -- cyclic graph

Track the path and stop on repeats: WHERE NOT next_node = ANY (r.visited)

On data with cycles a recursive CTE never runs out of new rows and loops until it runs out of memory or time.

What are the two parts of a recursive CTE?

The non-recursive anchor (the starting rows) and the recursive term, joined with UNION [ALL], which references the CTE itself and runs until it returns no rows.