runtimetask IN / EXISTS / NOT EXISTS · hard
Reviewers who cover every team repository
Return developer_id and team_id for developers whose team owns at least two active (unarchived) repositories and who reviewed at least one pull request in every one of them.
what to return
- Columns
developer_idteam_idin this order- Rows
- 673 rows · any order
- Skill
- Require a relationship with every member of a set. Refresher: NOT IN and the NULL trap →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result673 rows · any order
| developer_id | team_id |
|---|---|
| 5 | 4 |
| 17 | 4 |
| 19 | 4 |
first 3 of 673 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.
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
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
pull_requests10 columns · 13,242 rows
| pull_request_idnumber · unique id | repository_idnumber · links to repositories | author_idnumber · links to developers | titletext | created_atdate & time | merged_atdate & time | closed_atdate & time | statetext | additionsnumber | deletionsnumber |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 24 | Fix rate limiter | 2024-01-27 05:50:25 | NULL | 2024-02-16 05:29:35 | closed | 47 | 10 |
| 2 | 1 | 58 | Fix dashboard query | 2024-02-10 17:45:14 | 2024-02-11 12:07:27 | 2024-02-11 12:07:27 | merged | 16 | 5 |
| 3 | 1 | 9 | Fix cache invalidation | 2024-02-12 12:03:26 | 2024-02-12 18:28:29 | 2024-02-12 18:28:29 | merged | 763 | 365 |
| 4 | 1 | 24 | Migrate retry logic | 2024-02-14 09:27:38 | 2024-02-16 01:04:57 | 2024-02-16 01:04:57 | merged | 161 | 53 |
| 5 | 1 | 9 | Remove login flow | 2024-03-10 14:32:05 | 2024-03-11 04:00:50 | 2024-03-11 04:00:50 | merged | 396 | 129 |
| 6 | 1 | 58 | Migrate cache invalidation | 2024-03-12 10:11:26 | 2024-03-12 17:06:25 | 2024-03-12 17:06:25 | merged | 71 | 51 |
- pull_request_idnumber · unique id
- 1
- repository_idnumber · links to repositories
- 1
- author_idnumber · links to developers
- 24
- titletext
- Fix rate limiter
- created_atdate & time
- 2024-01-27 05:50:25
- merged_atdate & time
- NULL
- closed_atdate & time
- 2024-02-16 05:29:35
- statetext
- closed
- additionsnumber
- 47
- deletionsnumber
- 10
- pull_request_idnumber · unique id
- 2
- repository_idnumber · links to repositories
- 1
- author_idnumber · links to developers
- 58
- titletext
- Fix dashboard query
- created_atdate & time
- 2024-02-10 17:45:14
- merged_atdate & time
- 2024-02-11 12:07:27
- closed_atdate & time
- 2024-02-11 12:07:27
- statetext
- merged
- additionsnumber
- 16
- deletionsnumber
- 5
- pull_request_idnumber · unique id
- 3
- repository_idnumber · links to repositories
- 1
- author_idnumber · links to developers
- 9
- titletext
- Fix cache invalidation
- created_atdate & time
- 2024-02-12 12:03:26
- merged_atdate & time
- 2024-02-12 18:28:29
- closed_atdate & time
- 2024-02-12 18:28:29
- statetext
- merged
- additionsnumber
- 763
- deletionsnumber
- 365
- pull_request_idnumber · unique id
- 4
- repository_idnumber · links to repositories
- 1
- author_idnumber · links to developers
- 24
- titletext
- Migrate retry logic
- created_atdate & time
- 2024-02-14 09:27:38
- merged_atdate & time
- 2024-02-16 01:04:57
- closed_atdate & time
- 2024-02-16 01:04:57
- statetext
- merged
- additionsnumber
- 161
- deletionsnumber
- 53
- pull_request_idnumber · unique id
- 5
- repository_idnumber · links to repositories
- 1
- author_idnumber · links to developers
- 9
- titletext
- Remove login flow
- created_atdate & time
- 2024-03-10 14:32:05
- merged_atdate & time
- 2024-03-11 04:00:50
- closed_atdate & time
- 2024-03-11 04:00:50
- statetext
- merged
- additionsnumber
- 396
- deletionsnumber
- 129
- pull_request_idnumber · unique id
- 6
- repository_idnumber · links to repositories
- 1
- author_idnumber · links to developers
- 58
- titletext
- Migrate cache invalidation
- created_atdate & time
- 2024-03-12 10:11:26
- merged_atdate & time
- 2024-03-12 17:06:25
- closed_atdate & time
- 2024-03-12 17:06:25
- statetext
- merged
- additionsnumber
- 71
- deletionsnumber
- 51
first 6 of 13,242 rows · your queries use all of them
reviews5 columns · 19,622 rows
| review_idnumber · unique id | pull_request_idnumber · links to pull_requests | reviewer_idnumber · links to developers | submitted_atdate & time | statetext |
|---|---|---|---|---|
| 1 | 8775 | 1986 | 2024-01-01 04:49:28 | approved |
| 2 | 1017 | 245 | 2024-01-01 04:49:34 | commented |
| 3 | 10586 | 2340 | 2024-01-01 06:55:14 | commented |
| 4 | 11308 | 2551 | 2024-01-01 07:54:01 | changes_requested |
| 5 | 9777 | 2204 | 2024-01-01 12:48:19 | approved |
| 6 | 827 | 79 | 2024-01-01 16:04:29 | approved |
- review_idnumber · unique id
- 1
- pull_request_idnumber · links to pull_requests
- 8775
- reviewer_idnumber · links to developers
- 1986
- submitted_atdate & time
- 2024-01-01 04:49:28
- statetext
- approved
- review_idnumber · unique id
- 2
- pull_request_idnumber · links to pull_requests
- 1017
- reviewer_idnumber · links to developers
- 245
- submitted_atdate & time
- 2024-01-01 04:49:34
- statetext
- commented
- review_idnumber · unique id
- 3
- pull_request_idnumber · links to pull_requests
- 10586
- reviewer_idnumber · links to developers
- 2340
- submitted_atdate & time
- 2024-01-01 06:55:14
- statetext
- commented
- review_idnumber · unique id
- 4
- pull_request_idnumber · links to pull_requests
- 11308
- reviewer_idnumber · links to developers
- 2551
- submitted_atdate & time
- 2024-01-01 07:54:01
- statetext
- changes_requested
- review_idnumber · unique id
- 5
- pull_request_idnumber · links to pull_requests
- 9777
- reviewer_idnumber · links to developers
- 2204
- submitted_atdate & time
- 2024-01-01 12:48:19
- statetext
- approved
- review_idnumber · unique id
- 6
- pull_request_idnumber · links to pull_requests
- 827
- reviewer_idnumber · links to developers
- 79
- submitted_atdate & time
- 2024-01-01 16:04:29
- statetext
- approved
first 6 of 19,622 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.