Team size beneath each VP

Constraints

1. 1Each employee must be attributed to exactly one VP — the one whose subtree they are in. Carry the subtree root through the recursion rather than trying to work it out afterwards.
2. 2team_size excludes the VP themselves, so a VP with three reports and nobody below them scores 3, not 4.
3. 3The CEO is not a VP and does not appear in the output.
4. 4Aggregate outside the recursive CTE.
5. 5The three subtrees are very different sizes — that difference is the point of the report, so do not filter any of them out.
6. 6Order by team_size descending, then manager ascending.

Worked example

The anchor selects the three VPs and sets root_id to each VP's own id. The recursive term then copies r.root_id unchanged while walking down, so every descendant carries the id of the VP whose subtree it belongs to. Bo's subtree holds twelve rows including Bo herself, so team_size is 11. Chen's holds six, giving 5, and Dara's holds three, giving 2. The COUNT(*) - 1 is doing that exclusion. Anchoring on manager_id = <vp> instead would avoid the subtraction — but then a VP with no reports at all would vanish from the result rather than reporting zero.

What this tests

Carrying a value unchanged through a recursion so a subtree can be aggregated — the step that turns a tree walk into a report.