# The Weak Link > Build failures happen. Which repos break the most? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem The platform team is reviewing CI reliability and wants to know which repositories break the most. For each repository, return the percentage of its builds that ended in a 'failed' status, rounded to two decimals and worst first, breaking any ties by repository name. ## 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 **Conditional aggregation for failure rate per repo** ```sql 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 ORDER BY failure_pct DESC, repo_name ``` > **Cost Analysis** > > Single-pass hash aggregate on repo_name: one scan of ci_builds, conditional counters accumulated per group, no self-join and no subquery. On 2M rows this is the cheapest shape available for a per-group rate. No NULLIF guard is needed here because GROUP BY only emits groups that already have at least one row, so COUNT(*) is never zero. > **Interviewers Watch For** > > Watch whether the candidate reaches for SUM(CASE ...) or COUNT(CASE ...). SUM adds the 1 and 0 flags and yields the true failure count; COUNT tallies every row the CASE returns, zeros included. Choosing SUM (or COUNT with no ELSE) is the tell that they understand what CASE actually returns. > **Common Pitfall** > > Using COUNT(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) counts all rows, because the ELSE 0 is still a non-null value that COUNT tallies. Use SUM instead, or drop the ELSE so non-failures become NULL and COUNT skips them. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_weak_link) - [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.