# Many Eyes > Every codebase draws its own circle of watchers. Count who shows up. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem We're mapping how review attention spread across each codebase year over year. For every repo in a given calendar year, find how many different people reviewed its code, reporting the year as a whole number. ## 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 **Distinct reviewers per repo per year** ```sql 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 ``` > **The one word that decides the answer** > > The whole problem turns on one word. `COUNT(DISTINCT reviewer)` answers 'how many people'; `COUNT(*)` answers 'how many reviews'. They only agree when every reviewer touched a repo-year exactly once, which real data never does. When in doubt, ask yourself which noun the header names, then count that. > **Grain and the distinct count are the tell** > > Whether you count `DISTINCT` reviewers instead of rows, and whether the year sits in the `GROUP BY` rather than just the projection. A candidate who groups on the raw `opened_at` instead of the extracted year is a tell: they will emit one bucket per date, not per year, and never notice because the sample looks plausible. > **The year comes back as text** > > `strftime('%Y', opened_at)` returns TEXT ('2026'), not a number. Grouping still works, but the output column is a string, and any downstream numeric comparison or join on year silently misbehaves. Wrap it in `CAST(... AS INTEGER)` so `review_year` is a real integer, matching the expected shape. > **One scan, a tiny grouped set** > > At 500K rows this is a single scan feeding a hash aggregate keyed on (`repo_name`, year), and `repo_name` has only ~80 distinct values, so the grouped set stays tiny. The `DISTINCT` inside the aggregate is the real work: it maintains a per-group set of reviewers. A composite index on (`repo_name`, `opened_at`, `reviewer`) lets the planner group and dedup without a separate sort. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/many_eyes) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.