The Weak Link

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

The platform team is reviewing CI reliability and wants to understand how often each repository's builds break. For each repository, return the percentage of its builds that ended in a 'failed' status, rounded to two decimals.

Build failures happen. Which repos break the most?

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

Strip the CI costume off and this is a per-group rate: for each repo, the fraction of builds whose status is 'failed'. Anyone can filter to failed rows and count them, but a raw count of failures ranks the busiest repos on top, not the flakiest. The real move is dividing the failed count by the group's total inside a single GROUP BY. The trap is reaching for COUNT(CASE WHEN status = 'failed' THEN 1 ELSE 0 END): COUNT tallies the ELSE zeros too, so every repo comes back at 100% and the leaderboard is meaningless.

Building the rate

Step 1: Flag failures per repo

Inside each repo group, turn every build into 1 when its status is 'failed' and 0 otherwise, then SUM those flags. That sum is the failed-build count for the repo, computed in the same pass as the total.

Step 2: Turn the count into a rate

Divide the failed sum by COUNT(*) for the group and multiply by 100.0. The 100.0 literal forces float division, so you get 25.0 rather than an integer-truncated 0. ROUND to two decimals.

The solution

SELECT repo_name,
       ROUND(100.0 * SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) / COUNT(*), 2) AS failure_pct
FROM ci_builds
GROUP BY repo_name

Common follow-up questions

• How would you compute the global failure rate across all repos? (Tests removing the GROUP BY.)
• How would you track each repo's failure rate month over month? (Tests date truncation with GROUP BY.)
• How would you avoid a repo with two builds and one failure topping the list at 50 percent? (Tests awareness that small samples inflate rates.)