Top Commit Authors by Repo

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

Domain

SQL

Difficulty

hard

Seniority

L5

Problem

We're building a per-repo contributor leaderboard from our commit history, where an author's weight is the total lines they have added and only authors past 1,750 added lines make the board. For each repo, show its top three qualifying authors from the heaviest down, each written as the author's name, their line total, and their share of all the lines added across the entire codebase as a whole number percent. Where a repo has fewer than three qualifying authors, label the empty slots (for example 'No second author').

Three authors per repo. The top committers.

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

What this really is

This is a qualified top-N-per-group wearing a leaderboard costume, and it hides three separate teeth. Anyone can total SUM(added) per author. The skill is doing four things in the right order: drop everyone under the 1,750-line floor BEFORE you rank, rank the survivors inside each repo, compute each author's share of the whole codebase's added lines with REAL division, and pivot exactly three slots across one row with a label where a repo runs short. Rank before you filter and a light contributor steals a slot; divide two integers and 4.99% floors to 4; skip the CAST(ROUND(...) AS INTEGER) and the cell reads 5.0%; skip the COALESCE and a repo with two heavy authors shows a NULL where a name belongs.

Working through it

Step 1: Total lines added per author

GROUP BY repo_name, author with SUM(added) AS total_added. This is the expensive scan-and-aggregate, and everything downstream runs on this small grouped result, not the raw commits. Note that bob and Bob group separately: they are distinct accounts, not one contributor folded together.

Step 2: Apply the 1,750-line floor before ranking

WHERE total_added >= 1750 on the grouped totals, BEFORE the window runs. Filtering the per-author total here (not after ranking) means a below-floor author never gets a rank number and so cannot occupy a top-three slot. It also thins several repos below three qualifiers (frontend keeps only alice), which is what makes the placeholder necessary in the first place.

Step 3: Each author's share of the codebase

An uncorrelated scalar subquery (SELECT SUM(added) FROM repo_commits) gives the codebase-wide total across every repo and author. 100.0 * total_added makes the numerator a real, forcing real division, then CAST(ROUND(...) AS INTEGER) renders a clean whole-number percent. Write 100 * instead of 100.0 * and integer division truncates every share a point low; drop the outer CAST(... AS INTEGER) and ROUND leaves a 5.0 that reads 5.0%.

Step 4: Rank inside each repo

ROW_NUMBER() OVER (PARTITION BY repo_name ORDER BY total_added DESC, author ASC). The author ASC is a deterministic tiebreak, so two equal totals never swap the winner between runs.

Step 5: Pivot ranks 1, 2, 3 into columns

Conditional aggregation: MAX(CASE WHEN rn = 1 THEN ... END) collapses the ranked rows down to one row per repo with three named columns. Wrap the second and third slots in COALESCE(..., 'No nth author') so a rank the repo cannot fill reads as a label, not a NULL. Rank one needs no COALESCE: any repo that reaches the board has at least one qualifier.

Step 6: Format the cell

Concatenate author || ' (' || total_added || ' lines, ' || codebase_share_pct || '%)' so a cell reads bob (2330 lines, 5%).

The solution

WITH per_author AS (
  SELECT repo_name, author, SUM(added) AS total_added
  FROM repo_commits
  GROUP BY repo_name, author
),
ranked AS (
  SELECT repo_name, author, total_added,
         CAST(ROUND(100.0 * total_added / (SELECT SUM(added) FROM repo_commits)) AS INTEGER) AS codebase_share_pct,
         ROW_NUMBER() OVER (PARTITION BY repo_name
                            ORDER BY total_added DESC, author ASC) AS rn
  FROM per_author
  WHERE total_added >= 1750
)
SELECT repo_name,
       MAX(CASE WHEN rn = 1 THEN author || ' (' || total_added || ' lines, ' || codebase_share_pct || '%)' END) AS best_author,
       COALESCE(MAX(CASE WHEN rn = 2 THEN author || ' (' || total_added || ' lines, ' || codebase_share_pct || '%)' END), 'No second author') AS second_best_author,
       COALESCE(MAX(CASE WHEN rn = 3 THEN author || ' (' || total_added || ' lines, ' || codebase_share_pct || '%)' END), 'No third author') AS third_best_author
FROM ranked
GROUP BY repo_name

Integer division (share reads low)

100 * total_added / (SELECT SUM(added) FROM repo_commits) truncates: bob's 4.99 percent renders as 4, and every share on the board comes out a point light.

Real division, then integer cast (correct share)

CAST(ROUND(100.0 * total_added / (SELECT SUM(added) FROM repo_commits)) AS INTEGER): 4.99 rounds to 5 and the cast strips the .0, the whole-number percent the leaderboard expects.

Common follow-up questions

Common follow-up questions

• What changes if two authors tie on `total_added` and the spec says keep both? (Forces a `DENSE_RANK` versus `ROW_NUMBER` discussion and shows how the pivot breaks when one slot holds two names.)
• How would you measure each share against the author's own repo total instead of the whole codebase? (Probes whether the candidate swaps the codebase-wide denominator for a per-repo one and reuses the qualifying CTE instead of the raw table.)
• What if you need the top 10 per repo, not the top 3? (Tests whether the pivot pattern scales, or whether they switch to a long format with one row per (repo, rank).)
• How would you exclude bot authors like `dependabot[bot]`? (Opens up a `dim_author` join or a name-pattern filter instead of a hardcoded list.)