#126 SQL and analytics 6.8 min
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.
Climb from someone else
Copy the code folder first. In queries.py, the chain query starts from the name Jo. Change it to Gus and run python run.py chain. Predict the list of names before you run it, using the STAFF table in org.py. On this machine it prints 4 rows: Gus, Dina, Ben, Asha.
A hint
Each person's manager_id is the id of the next person up.
Shorten the limit
In queries.py, the loop query ends with LIMIT 7. Change it to LIMIT 4 and run python run.py loop. Predict how many rows come back and whether Ash appears a second time. On this machine it prints 4 rows ending at hop 2 and Ash does not come round again.
A hint
The limit stops the recursion after that many rows have been produced.
Add a town to the end
In org.py, add a route from Elm to a new town Fox, by adding ("Elm", "Fox") to ROUTES. Predict how many rows python run.py reach and python run.py paths print before you run them. On this machine reach prints 6 towns, and paths prints 9 rows, because Fox is reached by two different paths.
A hint
Elm can be reached in two ways, so anything after Elm can be reached in two ways too.
Add a person under Jo
In org.py, add one more person to STAFF with id 11, name Kit and manager 10, which is Jo. Predict the new team count for Asha and the depth that Kit will have in python run.py tree, then run python run.py team tree to check.
A hint
Everyone below a manager counts in the team query, not only direct reports.
These tasks are for you to try on your own machine. Nothing is checked or saved.
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.
