Repos with More Builds Than Commits

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

Only a passing build counts toward CI coverage, and a commit in repo_commits that adds more than 350 lines is a major change. The release team wants every repo in ci_builds whose successful builds at least match its major commits, showing its successful build count, highest first.

More builds than commits. Something is off.

Tables

ci_builds

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

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

This is a comparison of two independently filtered counts dressed up as CI coverage. Each side is easy on its own: passing builds per repo from ci_builds, and major commits per repo from repo_commits. The trap is the join that puts them side by side. Matching on repo_name pairs every passing build with every major commit in that repo, so the 6 builds and 6 commits for 'frontend' become 36 joined rows. If you count rows instead of ids, the comparison stops deciding anything: you get all six repos or none, never the three that actually keep pace.

Getting there

Step 1: Filter each side to what counts

cb.status = 'success' keeps the builds that validated something, and rc.added > 350 keeps the major commits. Each condition touches only its own table's columns, so writing both in WHERE is the same as filtering each table before the join.

Step 2: Join on repo_name and expect the fan-out

The two sides share only the repo, so the join is many-to-many. Every surviving row is one build paired with one commit, and no row on its own means a single build or a single commit any more.

Step 3: Count ids, not rows

COUNT(DISTINCT cb.build_id) collapses the 36 rows for 'frontend' back to its 6 builds, and COUNT(DISTINCT rc.commit_id) does the same for its commits. Without DISTINCT on either side, that side's count becomes the product of the two.

Step 4: Compare after grouping, then sort

The test is between two per-repo aggregates, so it belongs in HAVING, and equality passes. Sort by successful_builds descending so the busiest qualifying repo leads.

SELECT cb.repo_name,
       COUNT(DISTINCT cb.build_id) AS successful_builds
FROM ci_builds AS cb
INNER JOIN repo_commits AS rc
  ON cb.repo_name = rc.repo_name
WHERE cb.status = 'success'
  AND rc.added > 350
GROUP BY cb.repo_name
HAVING COUNT(DISTINCT cb.build_id) >= COUNT(DISTINCT rc.commit_id)
ORDER BY successful_builds DESC

On the seed, 'data-platform' has 8 passing builds and 8 major commits, and it leads. 'frontend' and 'backend-api' each have 6 of both, so they tie, and either can come first. 'infra' (6 vs 8), 'ml-pipeline' (6 vs 12) and 'mobile-app' (8 vs 12) fall short and drop out.

Counting joined rows

COUNT(*) >= COUNT(*) compares 36 to 36 for 'frontend' and 72 to 72 for 'ml-pipeline'. Both sides are the same product, so every repo passes and all six come back.

Counting ids

COUNT(DISTINCT cb.build_id) >= COUNT(DISTINCT rc.commit_id) compares 6 to 6 and 6 to 12. 'frontend' stays, 'ml-pipeline' drops, and three repos remain.

Common follow-up questions

• How would you rewrite this so the join never fans out? (Pre-aggregate each side to one row per `repo_name` before joining; the `DISTINCT` becomes unnecessary.)
• What if a repo with passing builds but no major commits should count as keeping pace? (An outer join, with zero substituted for the missing side's count.)
• How would you show how far behind the failing repos are? (Return both counts and their difference instead of filtering in `HAVING`.)