runtimetask Self joins · medium
Developers who review each other
Return developer_a, developer_b (developer_a < developer_b), a_reviewed_b and b_reviewed_a (review counts each way) for pairs who have each reviewed the other at least 3 times.
what to return
- Columns
developer_adeveloper_ba_reviewed_bb_reviewed_ain this order- Rows
- 604 rows · any order
- Skill
- Keep relationships that hold in both directions, listed once. Refresher: Self-joins & referral relationships →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result604 rows · any order
| developer_a | developer_b | a_reviewed_b | b_reviewed_a |
|---|---|---|---|
| 6 | 12 | 5 | 11 |
| 6 | 32 | 9 | 14 |
| 6 | 48 | 4 | 7 |
first 3 of 604 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.
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
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.