SQL pattern · P21 · 11 problems

Recursive CTEs and hierarchies

Graded problems on recursive CTEs and hierarchies, from easy to hard.

11 problems · about 2 h 30 min in all
Start with problem 01Depth of each employee in the org tree · 10 min
Learn it first · P21The join that finds only immediate childrenA single self-join follows one level; a recursive CTE repeats it to every descendant.Read the lesson →

Easy

3 problems · about 23 min
01Depth of each employee in the org tree10 min →02Generate a week of dates with a recursive CTE5 min →03Every ancestor of a category, up to the root8 min →

Medium

5 problems · about 1 h 10 min
04Everyone under a given manager12 min →05Build the path from root to each employee13 min →06Every team an incident can escalate to, despite the loops14 min →07Find the jobs caught in a dependency cycle14 min →08Fewest flights from London, with a route16 min →

Hard

3 problems · about 54 min
09Total reports (direct + indirect) per manager14 min →10Raw materials for a production run20 min →11Roll part costs up to every assembly20 min →
Up next · P22

The funnel that counts the wrong order

6 practice problems. Counting each stage independently credits users who did the steps out of sequence.

Practise P22Read P22