learning-it by example

#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.

1. What are the two parts of a recursive common table expression, and how are they joined?
2. On the made-up org chart of 10 people, how many people are under Asha, the person with no manager?
3. In the recursive org chart query, the chain query starts at Jo and climbs. Why does it stop at Asha after 4 steps?
4. The map has routes Ash to Bay, Bay to Cove, and Cove back to Ash. What stopped the loop query that used UNION ALL?
5. How does the recursive paths query on the town map keep the hop information and still avoid going round a loop?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. 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.

  2. 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.

  3. 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.

  4. 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.

Up next in SQL and analytics

More in SQL and analytics

All SQL and analytics videos

Related videos