runtimetask Self joins · medium
More pull requests than their manager
Return developer_id, prs, manager_id and manager_prs for developers who authored more pull requests than their manager. Developers and managers with no pull requests count as 0.
what to return
- Columns
developer_idprsmanager_idmanager_prsin this order- Rows
- 1153 rows · any order
- Skill
- Compare each row with a related row in the same table. Refresher: Self-joins & referral relationships →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result1153 rows · any order
| developer_id | prs | manager_id | manager_prs |
|---|---|---|---|
| 5 | 2 | 2 | 0 |
| 6 | 28 | 2 | 0 |
| 7 | 19 | 2 | 0 |
first 3 of 1153 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
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
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.