Top Repos by Successful Builds

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

ESPN

Problem

Only builds already marked success count; queued and running ones have not finished, and failed or canceled ones never will. A release engineer wants every repo in ci_builds with its number of successful builds, sorted from most successful to fewest.

Green builds. Which repos lead?

Tables

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 filtered count per group wearing a CI dashboard costume. Everyone writes the GROUP BY repo_name; what separates candidates is deciding which rows are allowed into the count before it happens. ci_builds mixes five statuses and only 'success' is a win: 'queued' and 'running' have not finished, 'failed' and 'canceled' never will. Skip the filter and the answer inverts: every repo lands at 32 or 34 builds, and data-platform, which actually leads with 8 successes, falls to fifth.

Building it

Step 1: Keep only builds marked 'success'

Put status = 'success' in WHERE so rows are filtered before anything is counted: 40 of the 200 rows survive. It cannot live in HAVING, because HAVING sees groups and a group of builds has no single status.

Step 2: Count the survivors per repo_name

GROUP BY repo_name with COUNT(*) counts rows. Count rows, not a column: COUNT(dur_secs) skips NULL durations, and every frontend build in the seed has a NULL dur_secs, so frontend would report 0 instead of 6.

Step 3: Sort most successful first

ORDER BY success_count DESC is the order the statement asks for. Six repos share only two counts (two at 8, four at 6), and the preview lists tied repos alphabetically, so repo_name ASC follows as the second key.

SELECT repo_name, COUNT(*) AS success_count
FROM ci_builds
WHERE status = 'success'
GROUP BY repo_name
ORDER BY success_count DESC, repo_name ASC

SELECT repo_name, COUNT(*) AS success_count
FROM ci_builds
GROUP BY repo_name
ORDER BY success_count DESC, repo_name ASC

Every build

No filter: backend-api, infra, ml-pipeline and mobile-app at 34, data-platform and frontend at 32. The column is named success_count but holds total volume.

Successful builds only

WHERE status = 'success' first: data-platform and mobile-app at 8, the other four at 6. The repo that leads on success sits fifth on raw volume.

Common follow-up questions

• If `status` held both 'SUCCESS' and 'success', how would you count both? (Tests case normalization: compare `LOWER(status) = 'success'` instead of the raw value.)
• Should a repo with zero successful builds appear with a 0? (Tests that a `WHERE` filter drops such repos entirely; keeping them needs a conditional count over all rows.)
• How would you add each repo's success rate next to the count? (Tests conditional aggregation: `SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) * 1.0 / COUNT(*)` over unfiltered rows, avoiding integer division.)