Recursive CTE Rollups

Recursive CTEs for Multi-Level Rollups

ROLLUP handles hierarchies stored as columns; a parent-child tree of varying depth needs a recursive CTE. BookNest groups its genres in mart.genre_tree (All books, then Fiction & Lit and Nonfiction, then Practical). The recursion pairs each leaf genre with every ancestor, so one join and one GROUP BY total every level:

Rolling sales up a genre tree of any depth

WITH RECURSIVE up AS (
  SELECT node AS genre, node AS ancestor, 0 AS hops FROM mart.genre_tree
  WHERE node NOT IN (SELECT parent FROM mart.genre_tree WHERE parent IS NOT NULL)
  UNION ALL
  SELECT up.genre, t.parent, up.hops + 1
  FROM up JOIN mart.genre_tree t ON t.node = up.ancestor
  WHERE t.parent IS NOT NULL
)
SELECT up.ancestor AS node, sum(s.gross_amount) AS gross, count(DISTINCT up.genre) AS genres
FROM up JOIN mart.sales s ON s.genre = up.genre AND s.status <> 'cancelled'
GROUP BY up.ancestor
ORDER BY gross DESC;

Output

      node       |   gross    | genres
-----------------+------------+--------
 All books       | 3303427.30 |      6
 Nonfiction      | 2076598.85 |      4
 Fiction & Lit   | 1226828.45 |      2
 ...
 Travel          |   90131.25 |      1

The anchor member selects the leaves; the recursive member climbs one level per iteration until no parent remains, so a deeper tree needs no query change. If bad data makes a node its own ancestor, the recursion never ends: guard it with the CYCLE clause (PostgreSQL 14 1,289 ) or WHERE up.hops < 20. The tree's INSERT is in demos/ch04/sql/443-tree.sql.

MENU