Reviewer Performance Metrics

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain
SQL
Difficulty
medium
Seniority
L4
Asked in
Google

Problem

The status text in code_reviews can't be trusted for the platform team's review audit, because only a merge date proves a pull request actually shipped. For reviews opened since the start of 2025, show each reviewer with at least one merged review, along with how many unique repos they reviewed, how many of their reviews were merged, and the most recent date one of their reviews was opened.

Some reviewers are thorough. Others are fast.

Tables

code_reviews

Columns: review_id INTEGER, repo_name TEXT, author TEXT, reviewer TEXT, status TEXT, comments INTEGER, opened_at TEXT, merged TEXT

code_reviews: 10 of 200 rows
review_idrepo_nameauthorreviewerstatuscommentsopened_atmerged
1053backend-apibobevechanges_requested32026-02-022026-02-05
1106infracharliefrankpending62026-03-032026-03-06
1159ml-pipelinedanagraceApproved92026-04-042026-04-07
1212mobile-appevehankPENDING122026-05-05NULL
1265data-platformfrankalicemerged152026-06-062026-06-09
1318frontendgracebobapproved182026-07-072026-07-10
1371backend-apihankcharliechanges_requested212026-08-082026-08-11
1424infraaliceNULLpending242026-09-09NULL
1477ml-pipelinebobeveApproved272026-01-102026-01-13
1530mobile-appcharliefrankPENDING302026-02-112026-02-14

Expected output

First 5 of 6 rows
reviewerrepos_reviewedmerged_reviewslatest_review_opened
alice3172026-09-06
bob3172026-09-07
charlie3172026-09-04
eve3182026-09-06
frank3182026-09-07

Worked solution and explanation

What this really is

This is two populations counted in one summary, dressed up as a reviewer audit. Every review in the window feeds the repo count; only the ones with a merge date feed the merged count. Anyone can write a GROUP BY reviewer. The trap is filtering merged rows out early, or trusting status, so both numbers describe the same rows. Get it wrong and every reviewer looks like they merged everything they touched.

Step 1: Slice the window on the open date

opened_at >= DATE '2025-01-01' compares a timestamp against a calendar boundary. The window is about when the review was opened; merged has nothing to do with it.

Step 2: Measure breadth with unique repos

A reviewer hits the same repo many times, so COUNT(*) measures volume, not breadth. COUNT(DISTINCT repo_name) gives how many different repos they touched.

Step 3: Tally merges by the date, not the text

status holds 'approved', 'Approved' and 'merged' on reviews that never shipped. A present merged date is the only proof, so the tally is COUNT(merged), never a match on the status label.

Step 4: Keep reviewers with at least one merge

The bar runs after grouping, so it sits in HAVING, spelled as a conditional tally: SUM(CASE WHEN merged IS NOT NULL THEN 1 ELSE 0 END) >= 1. It also removes the NULL reviewer bucket: unassigned reviews never merged, so that group scores zero.

Reviewer audit since the start of 2025
SELECT reviewer,
    COUNT(DISTINCT repo_name) AS repos_reviewed,
    COUNT(merged) AS merged_reviews,
    MAX(opened_at) AS latest_review_opened
FROM code_reviews
WHERE opened_at >= DATE '2025-01-01'
GROUP BY reviewer
HAVING SUM(CASE WHEN merged IS NOT NULL THEN 1 ELSE 0 END) >= 1
Filter first
WHERE merged IS NOT NULL before grouping: repos_reviewed shrinks to merged repos and merged_reviews equals the row count. Two columns, one population.
Count conditionally
Keep every review in the window and let the aggregates pick: COUNT(merged) sees only shipped rows, COUNT(DISTINCT repo_name) sees all of them. Same scan, different rows.

Common follow-up questions