learning-it by example

Back to the video

Recursive CTEs: Trees and Graphs in SQL

One recursive SQL query finds that Asha has 9 people under her; another goes round a loop until a LIMIT stops it.

Video #126 ยท SQL and analytics

Steps in the video

  1. 0:00 One query walks the whole org chart: Asha has 9 people under her
  2. 0:29 The idea
  3. 0:57 Setup
  4. 1:18 Ten people and five towns
  5. 1:42 Climb the chain
  6. 2:05 Walk down, count teams
  7. 3:14 A graph with a loop
  8. 4:21 Track the path
  9. 5:02 What this does not show
  10. 5:24 Mistakes to avoid
  11. 5:54 My take and next steps
  12. 6:20 Recap

Files

  • org.py (23 lines)
  • queries.py (49 lines)
  • run.py (11 lines)

Download the code (zip)

Tested with

Python 3.12.15

What this does not show

Ten made-up people and five made-up towns, run only in SQLite; text paths with instr will not scale to big graphs.

Sources

Learning IT by Example.