Fresh Ink

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

Asked in

Spotify

Problem

Find the three repositories with the most commits in repo_commits, busiest first, for the platform team's review-staffing plan. The plan counts only recent work: commits dated in the month of the newest commit on record or in any of the 24 calendar months before it.

Recent activity is the only activity that counts. Name the busiest hands.

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 problem really is

This is a top three over a data-anchored month window, dressed up as a staffing question. Anyone can count commits per repo_name. What separates candidates is where the window starts and what type the year-month is when it gets compared. The cutoff is the newest commit's month, two years back, read off repo_commits itself. The row's year-month has to be a number at the comparison. Get that wrong and SQLite compares the text '202202' against the number 202409, decides text always wins, and every commit since 2022 sneaks in: four repositories tie at 34 and the real leaders at 26 never show.

Step 1: Read the cutoff off the data

(SELECT strftime('%Y%m', MAX(commit_at)) - 200 FROM repo_commits) returns 202409 on this seed. The '%Y%m' format packs year and month into one sortable number, so subtracting 200 steps back exactly two years to the same month: the newest month plus the 24 before it. The subtraction coerces the text on its own, so this side is already numeric.

Step 2: Make the row's year-month a number before comparing

strftime('%Y%m', commit_at) returns text. A comparison, unlike arithmetic, does not coerce: SQLite orders every text value above every integer, so '202202' >= 202409 is true. Wrapping the row side in CAST(... AS INTEGER) is the one conversion that changes the answer.

Step 3: Count every commit per repo_name

GROUP BY repo_name with COUNT(*). Some commits carry a NULL message, and COUNT(message) would quietly drop them. A commit is a row, not a message.

Step 4: Sort busiest first and keep three

ORDER BY commit_count DESC LIMIT 3 gives 'ml-pipeline' and 'mobile-app' at 26, then 'data-platform' at 25. The leaders tie, so either order is correct. The other three sit at 23, so the cut after third place is clean.

SELECT repo_name, COUNT(*) AS commit_count
FROM repo_commits
WHERE CAST(strftime('%Y%m', commit_at) AS INTEGER) >=
      (SELECT strftime('%Y%m', MAX(commit_at)) - 200 FROM repo_commits)
GROUP BY repo_name
ORDER BY commit_count DESC
LIMIT 3

SELECT repo_name, COUNT(*) AS commit_count
FROM repo_commits
WHERE strftime('%Y%m', commit_at) >=
      (SELECT strftime('%Y%m', MAX(commit_at)) - 200 FROM repo_commits)
GROUP BY repo_name
ORDER BY commit_count DESC
LIMIT 3

Common follow-up questions

• How would the query change if the window ran back exactly 24 months from the newest `commit_at`, to the day? (Tests a date shift on `commit_at` itself and which boundary rows move.)
• If staffing covered the top three but a tie straddled third place, what would you return? (Tests tie handling at a `LIMIT` boundary: a tie rule, or returning every repository at the cutoff count.)
• How would you add each repository's number of different authors next to its commit count? (Tests adding a unique count alongside `COUNT(*)`, and noticing that `author` mixes case and padding.)