Average Review Comments by Author

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

L3

Asked in

Google

Problem

Engineering leadership is comparing review burden against tenure, treating each author handle as a case-sensitive identifier that must be matched exactly as it is stored. For every author who shows up in both the review log and the commit history, return the average number of comments across their code reviews alongside the date of their first commit.

Some authors get more feedback than others.

Tables

code_reviews

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

repo_commits

Columns: commit_id INTEGER, repo_name TEXT, author TEXT, message TEXT, added INTEGER, removed INTEGER, commit_at TEXT

Expected output

Worked solution and explanation

What this really is

Strip the business framing and this is two independent per-author rollups stitched together on the author key: one average over review comments, one minimum over commit timestamps. Anyone can write the two aggregates. The whole problem lives in the join key itself, because the commit data carries both 'Alice' and 'alice', and how you treat that casing decides who appears and what their earliest commit date is.

The trap: folding the case

It is tempting to look at 'Alice' and 'alice' and decide they are obviously the same person, then wrap the key in LOWER(author). That single decision quietly rewrites the answer. 'Alice' committed earlier than 'alice', so folding the case pulls that earlier commit into the merged identity and reports a too-early first-commit date for alice, which also bumps alice up the tenure ordering. The reviews side only ever uses lowercase handles, so case-sensitive grouping naturally drops the stray capitalized commit author through the inner join.

Case-folded (wrong)

LOWER(author) merges 'Alice' into 'alice'. alice's earliest_commit jumps to the earlier 'Alice' commit and alice sorts higher than it should.

Exact match (correct)

GROUP BY author keeps 'Alice' and 'alice' separate. 'Alice' has no matching review row and is dropped by the inner join; alice's earliest_commit reflects only its own commits.

Building it

Step 1: Average the comments per author

Roll up code_reviews to one row per author with AVG(comments). This average is taken over each author's own review rows only, which is the faithful 'average number of comments on their code reviews'.

Step 2: Find each author's earliest commit

Roll up repo_commits to one row per author with MIN(commit_at). That minimum timestamp is the author's first commit, the tenure signal the ordering depends on.

Step 3: Combine the two rollups on the author key

Inner join the two per-author rollups on author. The inner join restricts the result to authors present in both sources, which excludes pure reviewers with no commits (grace, hank) and the capitalized commit-only handle 'Alice'.

Step 4: Order most tenured first

Order by earliest_commit ascending so the longest-tenured author leads, with author as a deterministic tie-break for shared timestamps.

WITH review_stats AS (
    SELECT author, AVG(comments) AS avg_comments
    FROM code_reviews
    GROUP BY author
),
commit_stats AS (
    SELECT author, MIN(commit_at) AS earliest_commit
    FROM repo_commits
    GROUP BY author
)
SELECT r.author,
       r.avg_comments,
       c.earliest_commit
FROM review_stats r
JOIN commit_stats c ON r.author = c.author
;

Common follow-up questions

• Suppose product confirms 'Alice' and 'alice' really are the same engineer who changed their handle. How would you change the query, and what breaks if you normalize naively? (Tests whether the candidate can defend the deliberate case-sensitive grouping versus a real identity-resolution requirement.)
• What happens to `avg_comments` if you join `code_reviews` to `repo_commits` first and aggregate afterward, instead of aggregating each side independently? (Tests understanding of fan-out: joining the raw tables before aggregating inflates the average.)
• Both tables are large and you group by author every run. What indexing or pre-aggregation would keep these rollups off full table scans? (Tests index design for grouped aggregation over high-row tables.)
• How would you also surface authors who reviewed but never committed, showing a null earliest commit instead of dropping them? (Tests whether the candidate can switch the membership rule to an outer join.)