# Top Repos by Commit Volume > The most active repos in the org. No ties left behind. Canonical URL: 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. ## Tables ### repo_commits - commit_id INTEGER - repo_name TEXT - author TEXT - message TEXT - added INTEGER - removed INTEGER - commit_at TEXT Sample rows (10 of 200): | commit_id | repo_name | author | message | added | removed | commit_at | | --- | --- | --- | --- | --- | --- | --- | | 3061 | backend-api | bob | Add unit tests | 17 | 7 | 2026-02-02 01:09:00 | | 3122 | infra | charlie | Refactor auth module | 34 | 14 | 2026-03-03 02:18:00 | | 3183 | ml-pipeline | charlie | Update deps | 51 | 21 | 2026-04-04 03:27:00 | | 3244 | mobile-app | dana | Improve perf | 68 | 28 | 2026-05-05 04:36:00 | | 3305 | data-platform | frank | Add caching layer | 85 | 35 | 2026-06-06 05:45:00 | | 3366 | frontend | Alice | Fix typo in readme | 102 | 42 | 2026-07-07 06:54:00 | | 3427 | backend-api | Bob | NULL | 119 | 49 | 2026-08-08 07:03:00 | | 3488 | infra | alice | WIP | 136 | 56 | 2026-09-09 08:12:00 | | 3549 | ml-pipeline | bob | | 153 | 63 | 2026-01-10 09:21:00 | | 3610 | mobile-app | charlie | Add search endpoint | 170 | 70 | 2026-02-11 10:30:00 | ### ci_builds - build_id INTEGER - repo_name TEXT - branch TEXT - status TEXT - dur_secs INTEGER - trigger TEXT - built_at TEXT Sample rows (10 of 200): | build_id | repo_name | branch | status | dur_secs | trigger | built_at | | --- | --- | --- | --- | --- | --- | --- | | 5047 | backend-api | develop | failed | 34 | pull_request | 2026-02-02 01:13:00 | | 5094 | infra | feature/auth | canceled | 53 | schedule | 2026-03-03 02:26:00 | | 5141 | ml-pipeline | fix/login-bug | running | 72 | manual | 2026-04-04 03:39:00 | | 5188 | mobile-app | release/v2.0 | queued | 91 | tag | 2026-05-05 04:52:00 | | 5235 | data-platform | hotfix/payment | success | 110 | webhook | 2026-06-06 05:05:00 | | 5282 | frontend | feature /spaces | failed | NULL | push | 2026-07-07 06:18:00 | | 5329 | backend-api | main | canceled | 148 | pull_request | 2026-08-08 07:31:00 | | 5376 | infra | develop | running | 167 | schedule | 2026-09-09 08:44:00 | | 5423 | ml-pipeline | feature/auth | queued | 186 | manual | 2026-01-10 09:57:00 | | 5470 | mobile-app | fix/login-bug | success | 205 | tag | 2026-02-11 10:10:00 | ## Expected output First 4 of 4 rows: | repo_name | commit_count | standing | | --- | --- | --- | | infra | 34 | 1 | | ml-pipeline | 34 | 1 | | mobile-app | 34 | 1 | | data-platform | 32 | 4 | ## 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. **Top four pipeline repos with standings** ```sql 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 ``` > **A raw join to builds multiplies commits** > > `JOIN ci_builds b ON b.repo_name = c.repo_name` with the trigger filter in the outer `WHERE` pairs each commit with every non-PR build of its repo. `COUNT(*)` then reports commits times builds. Reduce `ci_builds` to one row per repo first. > **Ask where the cut lands** > > Before writing `LIMIT 4`, look at the fourth and fifth totals. Here they are equal, so the limit alone is a coin toss. **A tie on the line is the only time a tiebreaker earns its place**, and the statement tells you which one. **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. > **Say the tie out loud before the SQL** > > The strong signal is a candidate who notices two repos on 32 straddling the cut and asks who wins, and who spots that a join to raw build rows changes the grain. Both are grain-and-boundary questions, and naming them before typing is what reads as senior. > **Every step shrinks to repo grain early** > > The `ci_builds` scan collapses to a few hundred repo rows, so the join is a small hash join against a 15M-row `repo_commits` scan. The window sorts only the grouped repo rows and `LIMIT 4` makes the final sort a top-k. Joining raw builds would aggregate commits times builds, orders of magnitude more rows. --- ### 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/top_repos_by_commit_volume) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.