Top Repos by Commit Volume

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

Among the repos that ci_builds shows getting at least one build from something other than a pull-request check, which four have the most commits in repo_commits? The board has exactly four seats, filled most commits first and alphabetically when totals tie, and each row shows the repo's commit total and its standing among all of those pipeline repos.

The most active repos in the org. No ties left behind.

Tables

repo_commits

Columns: commit_id INTEGER, repo_name TEXT, author TEXT, message TEXT, added INTEGER, removed INTEGER, commit_at TEXT

ci_builds

Columns: build_id INTEGER, repo_name TEXT, branch TEXT, status TEXT, dur_secs INTEGER, trigger TEXT, built_at TEXT

Expected output

Worked solution and explanation

What this really is

This is a top-four cut with a tie sitting exactly on the line, dressed up as a repo leaderboard with a build-history filter in front of it. Anyone can count commits per repo. Two things separate candidates. First, repo_commits and ci_builds share only repo_name, so joining commits straight to builds pairs every commit with every qualifying build and inflates each total. Second, 'data-platform' and 'frontend' both sit on 32 commits and only one of them gets the fourth seat, so a sort by count plus a limit with no tie rule lets the engine pick. Get the first wrong and every number is fiction; get the second wrong and your answer changes between runs.

Walk it in order

Step 1: Collapse the builds to one row per repo

Keep the ci_builds rows whose trigger is not 'pull_request' and GROUP BY repo_name. That leaves one row per pipeline repo, so the join that follows filters repos without multiplying commits. 'backend-api' only ever ran pull-request checks, so it drops here despite its 34 commits.

Step 2: Count commits per surviving repo

Inner-join repo_commits to pipeline_repos on repo_name and take COUNT(*) per repo. Because the right side is one row per repo, each commit is counted exactly once.

Step 3: Stamp the standing before the cut

RANK() OVER (ORDER BY COUNT(*) DESC) runs over all five pipeline repos, so the three repos on 34 share standing 1 and 'data-platform' lands on 4, not 2. It is computed in the CTE, before LIMIT, so the cut never renumbers anyone.

Step 4: Sort, break the tie on the line, then cut

ORDER BY commit_count DESC, repo_name then LIMIT 4. The repo_name key only matters because the cut falls inside the 32 tie: alphabetical order hands the fourth seat to 'data-platform'. If the cut fell cleanly between two different totals, the tiebreaker would be dead weight.

WITH pipeline_repos AS (
  SELECT repo_name
  FROM ci_builds
  WHERE trigger <> 'pull_request'
  GROUP BY repo_name
),
repo_standings AS (
  SELECT c.repo_name,
         COUNT(*) AS commit_count,
         RANK() OVER (ORDER BY COUNT(*) DESC) AS standing
  FROM repo_commits c
  INNER JOIN pipeline_repos p ON p.repo_name = c.repo_name
  GROUP BY c.repo_name
)
SELECT repo_name, commit_count, standing
FROM repo_standings
ORDER BY commit_count DESC, repo_name
LIMIT 4

Row numbering

ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) gives 'infra', 'ml-pipeline' and 'mobile-app' standings 1, 2 and 3, inventing an order the data does not have, and 'data-platform' becomes 4 only by accident of position.

Shared standing

RANK() gives the three repos on 34 a shared 1 and puts 'data-platform' at 4, the number of repos ahead of it plus one. DENSE_RANK() would say 2, which is why the expected output is worth reading.

Where the interviewer goes next

Common follow-up questions

• What would the `standing` column show with `DENSE_RANK()` instead? ('data-platform' moves from 4 to 2; probes whether the candidate knows when positions skip after a tie.)
• Leadership now wants every repo tied for the fourth spot, not exactly four rows. What changes? (Replace `LIMIT 4` with a filter on `standing <= 4`, which returns five rows and makes the alphabetical tiebreaker unnecessary.)
• If the cut were the top three, would you still need `repo_name` in the `ORDER BY`? (The top three all sit on 34, so the cut lands cleanly after them and only presentation order among ties remains.)
• Now count only commits from the last 90 days. Where does that predicate go? (A `commit_at` filter belongs before the grouping so totals and standings both reflect the window.)