runtimetask Recursive CTEs · hard
Headcount of each team tree
For every top-level team (no parent), return team_id, subteams (descendant teams at any depth) and developers (current developers — left_at NULL — in the team or any descendant).
what to return
- Columns
team_idsubteamsdevelopersin this order- Rows
- 118 rows · any order
- Skill
- Roll a measure up a hierarchy. Refresher: Recursive CTEs →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result118 rows · any order
| team_id | subteams | developers |
|---|---|---|
| 1 | 4 | 42 |
| 2 | 0 | 6 |
| 3 | 0 | 5 |
first 3 of 118 rows
data you can query
Developer Platform dataset: the first 6 rows of each table. Under each column name: the kind of value it holds, and which table it links to. Your queries run on all rows.
teams4 columns · 324 rows
| team_idnumber · unique id | organization_idnumber · links to organizations | team_nametext | parent_team_idnumber · links to teams |
|---|---|---|---|
| 1 | 1 | Identity | NULL |
| 2 | 1 | Security | NULL |
| 3 | 1 | Messaging | NULL |
| 4 | 1 | Observability | 1 |
| 5 | 1 | Data | 4 |
| 6 | 1 | Storage | 4 |
- team_idnumber · unique id
- 1
- organization_idnumber · links to organizations
- 1
- team_nametext
- Identity
- parent_team_idnumber · links to teams
- NULL
- team_idnumber · unique id
- 2
- organization_idnumber · links to organizations
- 1
- team_nametext
- Security
- parent_team_idnumber · links to teams
- NULL
- team_idnumber · unique id
- 3
- organization_idnumber · links to organizations
- 1
- team_nametext
- Messaging
- parent_team_idnumber · links to teams
- NULL
- team_idnumber · unique id
- 4
- organization_idnumber · links to organizations
- 1
- team_nametext
- Observability
- parent_team_idnumber · links to teams
- 1
- team_idnumber · unique id
- 5
- organization_idnumber · links to organizations
- 1
- team_nametext
- Data
- parent_team_idnumber · links to teams
- 4
- team_idnumber · unique id
- 6
- organization_idnumber · links to organizations
- 1
- team_nametext
- Storage
- parent_team_idnumber · links to teams
- 4
first 6 of 324 rows · your queries use all of them
developers9 columns · 2,891 rows
| developer_idnumber · unique id | organization_idnumber · links to organizations | team_idnumber · links to teams | usernametext | display_nametext | roletext | manager_idnumber · links to developers | joined_atdate & time | left_attext |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | NULL | zgreen1 | Zara Green | vp | NULL | 2022-06-03 00:00:00 | NULL |
| 2 | 1 | 1 | dturner2 | Daniela Turner | director | 1 | 2022-07-03 00:00:00 | NULL |
| 3 | 1 | 2 | cthomas3 | Carlos Thomas | director | 1 | 2022-11-30 00:00:00 | NULL |
| 4 | 1 | 3 | mkang4 | Miguel Kang | director | 1 | 2022-09-29 00:00:00 | NULL |
| 5 | 1 | 4 | rnguyen5 | Ryan Nguyen | manager | 2 | 2023-07-27 00:00:00 | NULL |
| 6 | 1 | 5 | jabbott6 | Jose Abbott | manager | 2 | 2022-10-15 00:00:00 | NULL |
- developer_idnumber · unique id
- 1
- organization_idnumber · links to organizations
- 1
- team_idnumber · links to teams
- NULL
- usernametext
- zgreen1
- display_nametext
- Zara Green
- roletext
- vp
- manager_idnumber · links to developers
- NULL
- joined_atdate & time
- 2022-06-03 00:00:00
- left_attext
- NULL
- developer_idnumber · unique id
- 2
- organization_idnumber · links to organizations
- 1
- team_idnumber · links to teams
- 1
- usernametext
- dturner2
- display_nametext
- Daniela Turner
- roletext
- director
- manager_idnumber · links to developers
- 1
- joined_atdate & time
- 2022-07-03 00:00:00
- left_attext
- NULL
- developer_idnumber · unique id
- 3
- organization_idnumber · links to organizations
- 1
- team_idnumber · links to teams
- 2
- usernametext
- cthomas3
- display_nametext
- Carlos Thomas
- roletext
- director
- manager_idnumber · links to developers
- 1
- joined_atdate & time
- 2022-11-30 00:00:00
- left_attext
- NULL
- developer_idnumber · unique id
- 4
- organization_idnumber · links to organizations
- 1
- team_idnumber · links to teams
- 3
- usernametext
- mkang4
- display_nametext
- Miguel Kang
- roletext
- director
- manager_idnumber · links to developers
- 1
- joined_atdate & time
- 2022-09-29 00:00:00
- left_attext
- NULL
- developer_idnumber · unique id
- 5
- organization_idnumber · links to organizations
- 1
- team_idnumber · links to teams
- 4
- usernametext
- rnguyen5
- display_nametext
- Ryan Nguyen
- roletext
- manager
- manager_idnumber · links to developers
- 2
- joined_atdate & time
- 2023-07-27 00:00:00
- left_attext
- NULL
- developer_idnumber · unique id
- 6
- organization_idnumber · links to organizations
- 1
- team_idnumber · links to teams
- 5
- usernametext
- jabbott6
- display_nametext
- Jose Abbott
- roletext
- manager
- manager_idnumber · links to developers
- 2
- joined_atdate & time
- 2022-10-15 00:00:00
- left_attext
- NULL
first 6 of 2,891 rows · your queries use all of them
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.