Reviews Per Reviewer
An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.
- Domain
- SQL
- Difficulty
- easy
- Seniority
- L3
- Asked in
- General Assembly
Problem
The engineering manager is balancing review workloads and needs to see how many code reviews each reviewer is currently carrying.
The workload split across reviewers.
Tables
code_reviews
Columns: review_id INTEGER, repo_name TEXT, author TEXT, reviewer TEXT, status TEXT, comments INTEGER, opened_at TEXT, merged TEXT
| review_id | repo_name | author | reviewer | status | comments | opened_at | merged |
|---|---|---|---|---|---|---|---|
| 1053 | backend-api | bob | eve | changes_requested | 3 | 2026-02-02 | 2026-02-05 |
| 1106 | infra | charlie | frank | pending | 6 | 2026-03-03 | 2026-03-06 |
| 1159 | ml-pipeline | dana | grace | Approved | 9 | 2026-04-04 | 2026-04-07 |
| 1212 | mobile-app | eve | hank | PENDING | 12 | 2026-05-05 | NULL |
| 1265 | data-platform | frank | alice | merged | 15 | 2026-06-06 | 2026-06-09 |
| 1318 | frontend | grace | bob | approved | 18 | 2026-07-07 | 2026-07-10 |
| 1371 | backend-api | hank | charlie | changes_requested | 21 | 2026-08-08 | 2026-08-11 |
| 1424 | infra | alice | NULL | pending | 24 | 2026-09-09 | NULL |
| 1477 | ml-pipeline | bob | eve | Approved | 27 | 2026-01-10 | 2026-01-13 |
| 1530 | mobile-app | charlie | frank | PENDING | 30 | 2026-02-11 | 2026-02-14 |
Expected output
| reviewer | review_count |
|---|---|
| NULL | 24 |
| alice | 24 |
| bob | 24 |
| eve | 26 |
| frank | 26 |
Worked solution and explanation
This is a per-person tally of one table, and the easy part really is easy: one row per reviewer, one number each. What separates candidates is the 24 reviews nobody has picked up. They sit under a NULL reviewer, and two reflexes erase them: counting the column instead of the rows, or 'cleaning' the missing reviewer out. Either one hides from the manager exactly the pile they most needed to see.
Reading the request
Step 1: Decide what one output row is
One row per reviewer, and the unassigned reviews get a slot too. The preview's blank first line with 24 beside it is the tell: the answer keeps them as their own bucket instead of dropping them.
Step 2: Count reviews, not reviewer values
COUNT(*) counts the rows in each group. COUNT(reviewer) counts non-null reviewer values, so in the group whose key is NULL it returns 0, even though 24 reviews live there.
Step 3: Leave status and merged alone
'Currently carrying' sounds like a hint to filter on status or merged. Check it against the preview: the unfiltered tally matches it line for line, and any filter on review state pushes every number below it.
SELECT reviewer, COUNT(*) AS review_count
FROM code_reviews
GROUP BY reviewer- Counts the column
COUNT(reviewer)skips nulls, so the unassigned group still appears but reads 0. The other seven reviewers look right, which is why this slips through review.- Counts the rows
COUNT(*)counts every review in the group, so the unassigned bucket reports its true 24 next to eve's 26 and alice's 24.
Common follow-up questions
- How would you count only the reviews still waiting on someone, given `status` holds both 'pending' and 'PENDING'? (Tests normalizing case before filtering on a dirty categorical.)
- The dashboard shows a blank for the unassigned line. How would you label it? (Tests substituting a display value for `NULL` without losing the bucket.)
- How would you add each reviewer's share of all reviews next to the count? (Tests combining a grouped count with a total over the whole result.)