Busy Authors

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

Problem

An engineering manager is spotlighting the most prolific contributors, leaving out any commit whose message was never recorded (a blank message still counts). For each author credited with 24 or more of the remaining commits, show their commit total and how many repositories they've touched.

Some developers spread their commits everywhere.

Tables

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

The skill hiding under the commit log

This is an identity-folding aggregation with a data-quality filter, wearing a 'who is most active' costume. Two things have to happen before you count anything. First, fold each developer's case-variant names into one person: the author column was never normalized on write, so 'Alice' and 'alice' are one human typed two ways, and if you group on the raw column her 44 commits split into two piles of 22 that both fall under the cutoff, so your busiest engineer silently vanishes from the standings. Second, drop the commits whose message was never recorded (message IS NULL), since those are not tallyable work, while a blank empty-string message is still a genuine commit that counts. Fold, filter, then count.

Walking the query

Step 1: Fold the identity

Group by LOWER(TRIM(author)) so every casing (and any stray whitespace) of a name lands in one bucket. This is the decision the whole problem turns on: GROUP BY author scatters bob across 'bob' and 'Bob', so his real total of 46 never appears; GROUP BY LOWER(TRIM(author)) recovers the true contributors.

Step 2: Drop the never-recorded commits

The manager only wants commits that were actually logged with a message, so filter WHERE message IS NOT NULL. Watch the NULL-versus-blank distinction: a NULL message means nothing was ever recorded and is dropped, but an empty-string message ('') is a real if terse commit and stays. Reaching for TRIM(message) <> '' here would wrongly delete the blank commits too, and your totals would come out too low.

Step 3: Count two different things

COUNT(*) is raw commit volume (how busy the person is). COUNT(DISTINCT repo_name) is spread (how many separate codebases they touched). These are not the same number: bob has 46 commits across only 3 repos. The prompt asks for both, so compute both.

Step 4: Filter after aggregating

The '24 or more commits' cutoff is a property of the whole group, not of any single commit row, so it belongs in HAVING COUNT(*) >= 24, which runs after the grouping. A WHERE cannot see a group-level count.

Step 5: Order the standings

Busiest first is commit_count DESC. Equal totals (charlie, dana and eve all land on 24) settle alphabetically by author so the ordering is stable and repeatable rather than whatever the engine happens to emit.

The solution

SELECT LOWER(TRIM(author)) AS author,
       COUNT(*) AS commit_count,
       COUNT(DISTINCT repo_name) AS repo_count
FROM repo_commits
WHERE message IS NOT NULL
GROUP BY LOWER(TRIM(author))
HAVING COUNT(*) >= 24

GROUP BY author (raw)

bob splits into 'bob' (24) and 'Bob' (22), alice into 'alice' (22) and 'Alice' (22). The 22s fall under the cutoff, so the two busiest engineers are cut down and the standings name the wrong people.

GROUP BY LOWER(TRIM(author))

Every spelling folds into one contributor: bob 46, alice 44. Five real people, correctly ranked, with charlie, dana and eve tied at 24.

Common follow-up questions

• The manager now wants only commits from the last 12 months to count toward the threshold. Where does that filter go, and why is it a WHERE rather than a HAVING? (Tests the WHERE-vs-HAVING distinction: a row-level date filter runs before aggregation, unlike the group-level count.)
• The manager later decides blank empty-string messages should NOT count either, only commits with real text. What single change do you make, and why is it more than just the NULL check? (Tests the NULL-versus-empty distinction and combining TRIM with the null filter.)
• How would you add an index so this query avoids re-lowercasing every row on each run? (Tests indexing strategy for expression-based grouping (functional index).)
• Two contributors tie on commit count and on name. What would you add to keep the ordering deterministic? (Tests awareness that ORDER BY needs a fully unique key to be stable.)