Many Eyes

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

BuzzFeed

Problem

Each row of code_reviews is one review of a repo, stamped with who reviewed it and the date it was opened. For every repo in each calendar year its reviews were opened, find how many different people reviewed its code, reporting the year as a whole number.

Every codebase draws its own circle of watchers. Count who shows up.

Tables

code_reviews

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

Expected output

Worked solution and explanation

What this really is

Strip off the code-review costume and this is a two-dimensional distinct count: for every (repo, year) pair, how many unique people reviewed the code. Anyone can type GROUP BY. The line that separates candidates is COUNT(DISTINCT reviewer) versus a plain COUNT: one reviewer who left five comments on the same repo in the same year is one person, not five. Miss the DISTINCT and you are reporting review VOLUME while your column header claims reviewer HEADCOUNT, inflating collaboration density by whatever the comment rate happens to be. The second quiet trap is the grain: the year has to live in the GROUP BY, not just the SELECT, or every year collapses into one bucket per repo.

Getting the grain right

Step 1: Make the year part of the key

The reporting unit is a repo in a specific year, so the year is part of the key, not decoration. Pull it out of opened_at with strftime('%Y', opened_at) and put that expression in BOTH the SELECT and the GROUP BY. If you only add it to SELECT, the engine still buckets by repo alone and you get one arbitrary year stamped on a repo's whole history.

Step 2: Count people, not rows

Reviewer is the thing we are counting, and people repeat. COUNT(DISTINCT reviewer) is the only form that answers 'how many different people', and it quietly does the right thing with unassigned reviews too: NULL reviewers are skipped, so a review with no one assigned does not pad the number.

Step 3: Make the year a real number

strftime('%Y', opened_at) returns TEXT ('2026'), and the answer wants a whole-number year, so wrap it in CAST(... AS INTEGER). The prompt asks for the year as a number, and this is where you honor it: review_year becomes a real integer instead of a string that only looks numeric.

The solution

SELECT repo_name, CAST(strftime('%Y', opened_at) AS INTEGER) AS review_year, COUNT(DISTINCT reviewer) AS reviewer_count
FROM code_reviews
GROUP BY repo_name, review_year

Common follow-up questions

• Some reviews have no reviewer assigned yet. Are those rows changing any of your counts, and is that the behavior you want? (Tests whether the candidate knows COUNT(DISTINCT) ignores NULLs and whether that is the desired semantics.)
• If the team decided the year should reflect when a review merged rather than when it opened, what changes, and what breaks for reviews that never merged? (Probes the difference between bucketing on `opened_at` versus merged and the NULL exposure of merged.)
• If this table grew to billions of rows, which part of the plan gets expensive first, and how would you keep the distinct reviewer count cheap? (Tests scaling intuition around the DISTINCT aggregate and grouping cardinality.)