Repository Commit Ranking

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

Airbnb

Problem

repo_commits holds one row per commit, with added recording the lines that commit introduced to its repository. Build the repository standings by total lines added, highest first, where repositories with equal totals share a position and the next total takes the following number.

Lines added tell the story of a repo's ambition.

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

This is a leaderboard over an aggregate, dressed as a commit log. The table holds 200 commit rows and the answer holds six repository rows, so the first decision is grain: the position is computed over SUM(added) after collapsing to repo_name, never over commits. Anyone can write the sum. What separates candidates is the numbering they pick for the position and whether they can say what it does when two totals tie. DENSE_RANK() lets equal totals share a spot and gives the next total the very next number. Pick by habit and the first week two repos tie, the published board reads 1, 1, 3.

Getting there

Step 1: Collapse commits to one row per repository

GROUP BY repo_name with SUM(added) turns 200 commits into six totals. Every later decision runs on those six rows, so get the grain right before you think about positions.

Step 2: Order the window by the aggregate itself

Window functions are evaluated after grouping, so DENSE_RANK() OVER (ORDER BY SUM(added) DESC) sees the six grouped rows, and the SUM(added) inside OVER is each group's total. There is no need for a CTE to name the total first.

Step 3: Sort by the position

ORDER BY rank lists the standings top to bottom. It is the same order as total descending, and it reads the way the board is published.

SELECT repo_name, SUM(added) AS total_added, DENSE_RANK() OVER (ORDER BY SUM(added) DESC) AS rank
FROM repo_commits
GROUP BY repo_name
ORDER BY rank

Skipping numbering

With RANK(), two repos tied at 7,900 both get 3 and the next repo gets 5. The board has no fourth place.

Gap-free numbering

With DENSE_RANK(), the same two repos both get 3 and the next repo gets 4. Equal totals share a spot and no number is skipped.

SELECT repo_name, added, DENSE_RANK() OVER (ORDER BY added DESC) AS rank
FROM repo_commits

Common follow-up questions

• Return only the top three positions, keeping every repo that ties into third. (Filtering on the dense position in an outer query or with `QUALIFY`, rather than `LIMIT 3`, which cuts through a tie.)
• Publish the standings per quarter of `commit_at` instead of all time. (Grouping by repo and quarter, then restarting the numbering with `PARTITION BY` on the quarter.)
• What changes if `added` can be `NULL` for merge commits? (`SUM` skips NULLs, so a repo with only NULL commits gets a NULL total and lands wherever the engine places NULLs in a descending sort.)