Commit Royalty

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
Spotify

Problem

Find the repository with the most commits in a monthly top 10 by lines added, along with how many it placed. We build a separate top 10 for each month of 2026 from repo_commits, taking the ten biggest commits per month across all repositories.

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

repo_commits: 10 of 200 rows
commit_idrepo_nameauthormessageaddedremovedcommit_at
3061backend-apibobAdd unit tests1772026-02-02 01:09:00
3122infracharlieRefactor auth module34142026-03-03 02:18:00
3183ml-pipeline charlieUpdate deps51212026-04-04 03:27:00
3244mobile-appdana Improve perf68282026-05-05 04:36:00
3305data-platformfrank Add caching layer 85352026-06-06 05:45:00
3366frontendAliceFix typo in readme102422026-07-07 06:54:00
3427backend-apiBobNULL119492026-08-08 07:03:00
3488infraaliceWIP136562026-09-09 08:12:00
3549ml-pipelinebob153632026-01-10 09:21:00
3610mobile-appcharlieAdd search endpoint170702026-02-11 10:30:00

Expected output

First 1 of 1 rows
repo_nametop10_count
mobile-app17

Worked solution and explanation

What this really is

This is top-N-per-group dressed up as an engineering leaderboard. The question is not which repository has the biggest commit or the most commits. It is which repository keeps showing up among each month's ten biggest. Anyone can sort by added. The skill is making that order restart every calendar month and cutting at ten inside each month before you count. Every month of 2026 holds 13 or 14 commits, so the cut decides the answer. Skip it and you are just counting commits per repository ('mobile-app' at 21 instead of 17). Use one global ORDER BY added DESC LIMIT 10 and only ten commits qualify, leaving 'mobile-app' and 'ml-pipeline' tied at 3.

The trap: one global top 10
SELECT repo_name, COUNT(*) AS top10_count
FROM (
  SELECT repo_name
  FROM repo_commits
  WHERE strftime('%Y', commit_at) = '2026'
  ORDER BY added DESC
  LIMIT 10
) AS year_top
GROUP BY repo_name
ORDER BY top10_count DESC
LIMIT 1

Wrong: ten commits for the whole year, not ten per month.

Build it in three moves

Step 1: Keep only 2026 commits

Filter repo_commits with strftime('%Y', commit_at) = '2026' before anything else. Earlier years live in the same table, and each of their months would otherwise form its own top 10 and pad every count.

Step 2: Restart the order every month

Number commits with DENSE_RANK() over PARTITION BY strftime('%Y-%m', commit_at) ORDER BY added DESC. The partition key is the derived year-month, not the raw commit_at: partition on the timestamp and every commit sits alone in its partition and ranks 1. Rank on added alone, as asked, not on added - removed.

Step 3: Cut at ten, then count

Keep rnk <= 10 in the outer query, since a window result cannot be filtered in the WHERE of the SELECT that computes it. Then GROUP BY repo_name with COUNT(*). Every qualifying commit counts, so a repository holding four spots in one month gets four. The preview's 17 is more than nine months, which is how you know commits are counted, not months.

The solution

Rank inside each month, then count per repository
WITH monthly_ranked AS (
  SELECT repo_name,
         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 repo_name, COUNT(*) AS top10_count
FROM monthly_ranked
WHERE rnk <= 10
GROUP BY repo_name
ORDER BY top10_count DESC
LIMIT 1

Common follow-up questions