runtimetask LEFT JOIN · medium
Debug: teams without active repositories
Return team_id and team_name for teams that own no active (unarchived) repository. The query returns nothing useful. Fix it.
what to return
- Columns
team_idteam_namein this order- Rows
- 82 rows · any order
- Skill
- Repair the query’s logic and confirm the fix on changed data. Refresher: ON versus WHERE in a LEFT JOIN →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result82 rows · any order
| team_id | team_name |
|---|---|
| 3 | Messaging |
| 7 | Notifications |
| 8 | Payments |
first 3 of 82 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
repositories9 columns · 512 rows
| repository_idnumber · unique id | organization_idnumber · links to organizations | owning_team_idnumber · links to teams | repo_nametext | primary_languagetext | visibilitytext | created_atdate & time | archived_attext | forked_from_repository_idnumber · links to repositories |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 | notifier-catalog | Python | private | 2022-06-01 00:00:00 | NULL | NULL |
| 2 | 1 | 2 | billing-metrics | C# | internal | 2022-06-08 00:00:00 | NULL | NULL |
| 3 | 1 | 4 | catalog-cli | Go | private | 2022-07-23 00:00:00 | NULL | NULL |
| 4 | 1 | 4 | catalog-cli-fork | Python | internal | 2022-07-21 00:00:00 | NULL | 3 |
| 5 | 1 | 5 | cli-metrics | Go | private | 2023-07-18 00:00:00 | NULL | NULL |
| 6 | 1 | 5 | web-infra | Swift | internal | 2023-01-21 00:00:00 | NULL | NULL |
- repository_idnumber · unique id
- 1
- organization_idnumber · links to organizations
- 1
- owning_team_idnumber · links to teams
- 1
- repo_nametext
- notifier-catalog
- primary_languagetext
- Python
- visibilitytext
- private
- created_atdate & time
- 2022-06-01 00:00:00
- archived_attext
- NULL
- forked_from_repository_idnumber · links to repositories
- NULL
- repository_idnumber · unique id
- 2
- organization_idnumber · links to organizations
- 1
- owning_team_idnumber · links to teams
- 2
- repo_nametext
- billing-metrics
- primary_languagetext
- C#
- visibilitytext
- internal
- created_atdate & time
- 2022-06-08 00:00:00
- archived_attext
- NULL
- forked_from_repository_idnumber · links to repositories
- NULL
- repository_idnumber · unique id
- 3
- organization_idnumber · links to organizations
- 1
- owning_team_idnumber · links to teams
- 4
- repo_nametext
- catalog-cli
- primary_languagetext
- Go
- visibilitytext
- private
- created_atdate & time
- 2022-07-23 00:00:00
- archived_attext
- NULL
- forked_from_repository_idnumber · links to repositories
- NULL
- repository_idnumber · unique id
- 4
- organization_idnumber · links to organizations
- 1
- owning_team_idnumber · links to teams
- 4
- repo_nametext
- catalog-cli-fork
- primary_languagetext
- Python
- visibilitytext
- internal
- created_atdate & time
- 2022-07-21 00:00:00
- archived_attext
- NULL
- forked_from_repository_idnumber · links to repositories
- 3
- repository_idnumber · unique id
- 5
- organization_idnumber · links to organizations
- 1
- owning_team_idnumber · links to teams
- 5
- repo_nametext
- cli-metrics
- primary_languagetext
- Go
- visibilitytext
- private
- created_atdate & time
- 2023-07-18 00:00:00
- archived_attext
- NULL
- forked_from_repository_idnumber · links to repositories
- NULL
- repository_idnumber · unique id
- 6
- organization_idnumber · links to organizations
- 1
- owning_team_idnumber · links to teams
- 5
- repo_nametext
- web-infra
- primary_languagetext
- Swift
- visibilitytext
- internal
- created_atdate & time
- 2023-01-21 00:00:00
- archived_attext
- NULL
- forked_from_repository_idnumber · links to repositories
- NULL
first 6 of 512 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.