lesson 47 Advanced product analysis · advanced

Recursive CTEs

Repeat a query step to explore a hierarchy.

the idea

A recursive CTE has an anchor row and a recursive step joined by UNION ALL. The step uses the previous result until its stopping condition is false. Always include a termination condition.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

WITH RECURSIVE numbers(n) AS (
  SELECT 1
  UNION ALL
  SELECT n+1
  FROM numbers
  WHERE n<5
)
SELECT n
FROM numbers;

Watch for: An unbounded recursive query is interrupted by the app time limit.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Start from a base row. A recursive CTE needs an anchor member. Here the sequence begins at 1 before the recursive step adds 1.

   step-1.sql Run

   sql›

   SELECT 1 AS n;

2. 2

   Grow the sequence recursively. UNION ALL attaches rows produced from previous rows until the WHERE stop condition fails.

   step-2.sql Run

   sql›

   WITH RECURSIVE numbers(n) AS (
     SELECT 1
     UNION ALL
     SELECT n + 1
     FROM numbers
     WHERE n < 5
   )
   SELECT n
   FROM numbers;