lesson 47 Advanced product analysis · advanced

Recursive CTEs

Repeat a query step to explore a hierarchy.

the idea

A recursive CTE has an anchor query that returns the starting rows and a recursive query that joins the CTE to the table to find the next level. SQLite repeats the recursive step until it adds no rows. Carry a depth column to know how far each row is from the start.

Dataset: Developer Platform

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Count from 1 to 5 with recursion. Run this. A recursive CTE starts at 1 and keeps adding rows until the condition fails—the pattern you reuse for org trees.

   step-1.sql Run

   sql›

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

2. 2

   See how teams nest. Run this. parent_team_id links each team to its parent; NULL means a top-level team in organization 2.

   step-2.sql Run

   sql›

   SELECT team_id, team_name, parent_team_id
   FROM teams
   WHERE organization_id = 2
   ORDER BY parent_team_id NULLS FIRST, team_id;

3. 3

   Put it together. The lesson's pattern in one query.the pattern

   example.sql Run

   sql›

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

common mistake

Joining in the wrong direction (parent to child versus child to parent) walks up the tree instead of down, or stops after one level.