Top Committers in 2025

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

Problem

Each row of repo_commits is one commit, stamped with its commit_at time, the lines it added and its author, a name whose capitalization varies between commits by the same person. Across 2026, which author most often landed the month's biggest commit, and how many times? A level tally goes to the name that comes first alphabetically.

In a sea of commits, only a few wear the crown.

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

Under the commit-log costume, this is top-1 per group, then a win count. Everyone reaches for the window function. What separates candidates is scope, ties and identity. The ranking has to restart every month of 2026, not run across the whole year, and 'the month's biggest commit' has to mean every commit tied for the largest added, not one arbitrary row. Rank across the year and you keep a few giant commits instead of a winner per month; pick one row per month and a tied winner silently loses a win. And the log spells one person as 'Alice' and 'alice': count the raw strings and that person's wins split in two. Any of these makes the count wrong and, on a close race, the name too.

Build it in four moves

Step 1: Scope to 2026 before ranking

Put the year filter in the CTE's WHERE, which runs before the window function. Commits from other years then never get a rank and never add wins to anyone's tally. Leave it out and every author's count absorbs four other years of monthly winners.

Step 2: Rank within each month with DENSE_RANK()

PARTITION BY strftime('%Y-%m', commit_at) restarts the ranking every month, and ORDER BY added DESC puts the largest commit first. DENSE_RANK() hands rank 1 to every commit tied for the largest size, which is exactly what 'the month's biggest' means when two commits are the same size.

Step 3: Fold the name to one case with LOWER(author)

The log writes one person as 'Alice' and 'alice', and another as 'Bob' and 'bob'. Lower-case the name inside the CTE so the outer GROUP BY author sees one person once. Skip it and each of those people splits into two smaller tallies, and a different name floats to the top.

Step 4: Count monthly wins, then break the tie

Keep rnk = 1, then GROUP BY author with COUNT(*). Each surviving row is one month won. Sort by month_wins DESC, author and take LIMIT 1. Once spellings are folded, three names finish level here, so the secondary sort on author is what picks the answer.

WITH monthly_ranked AS (
  SELECT LOWER(author) AS author, added,
         DENSE_RANK() OVER (PARTITION BY strftime('%Y-%m', commit_at) ORDER BY added DESC) AS rnk
  FROM repo_commits
  WHERE strftime('%Y', commit_at) = '2026'
)
SELECT author, COUNT(*) AS month_wins
FROM monthly_ranked
WHERE rnk = 1
GROUP BY author
ORDER BY month_wins DESC, author
LIMIT 1

`ROW_NUMBER()`

If added is 900, 900, 700 in a month, ROW_NUMBER() returns 1, 2, 3, and which 900 gets the 1 is up to the engine. Filtering rn = 1 credits only one of the two biggest commits.

`DENSE_RANK()`

The same rows get 1, 1, 2. rnk = 1 keeps both 900s, so both authors are credited with that month, and the answer no longer depends on the engine's whim.

Common follow-up questions

• Return the top author for each repository instead of one overall. (Add `repo_name` to both the window's `PARTITION BY` and the outer `GROUP BY`, then rank the counts a second time.)
• What if a commit's size is `added + removed`? (Checks whether you can rank on a derived expression without writing it out twice.)
• How would you credit a month's three biggest sizes instead of only the largest? (Tests whether you see that a looser cutoff such as `rnk <= 3` changes what a win means, and how ties widen it.)
• A couple of author names carry stray spaces. What changes? (Tests whether you fold `TRIM` into the same normalization and check whether it moves the result.)