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

Looking at CI/CD health, find all repos where the number of builds meets or exceeds the number of commits. Show each repo with its build count, ranked from highest to lowest.

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

Why this problem exists in real interviews

The core skill being tested is self-join, applied to the ci_builds and repo_commits tables in a analytics context. Getting columns like build_id, repo_name, and commit_id right is where most candidates slip. The problem layers in HAVING for post-aggregation filtering as well.

Break down the requirements

Step 1: Join ci_builds to repo_commits

The join connects the two tables on their shared key. This brings the columns needed for filtering and aggregation into a single row set.

Step 2: Deduplicate the result with DISTINCT

SELECT DISTINCT removes duplicate rows from the output. This is necessary when joins or subqueries can produce repeated combinations.

The solution

SELECT cb.repo_name, COUNT(DISTINCT cb.build_id) AS build_count
FROM ci_builds cb
INNER JOIN repo_commits rc ON cb.repo_name = rc.repo_name
GROUP BY cb.repo_name
HAVING COUNT(DISTINCT cb.build_id) >= COUNT(DISTINCT rc.commit_id)
ORDER BY build_count DESC

Common follow-up questions

• The `dur_secs` column in `ci_builds` has a 2% null rate. How does your query handle rows where `dur_secs` is NULL, and could that silently change the result count? (Tests whether the candidate accounts for NULLs in `ci_builds.dur_secs` and understands how aggregates skip NULL values.)
• If `repo_commits` grew to contain billions of rows, which part of your query would become the bottleneck given the cardinality of `message`? (Tests ability to identify performance hotspots related to `repo_commits.message` at scale.)
• If the HAVING threshold in your query changed from a fixed number to a percentile, how would you restructure the query? (Tests ability to replace static HAVING filters with dynamic subquery-based thresholds.)