Build Success vs Failure by Repo

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

L3

Asked in

Tesla

Problem

The engineering lead needs a per-repo build health snapshot before freezing deploys. For each repository, show how many builds have a status of 'success', how many have a status of 'failure', and the average build duration.

Green versus red, repo by repo.

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

status carries more than two values (success, failure, plus running, canceled, timeout), but avg_duration is over every build. The conditional aggregation has to count two values and average across all of them in one pass.

Break down the requirements

Step 1: Group once per repo

Single GROUP BY repo_name over the 3M row table. Every metric lives in the same aggregate, no joins or subqueries.

Step 2: Branch the counts with CASE

SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) and the mirror for 'failure'. The ELSE 0 keeps in-flight and canceled builds in the table without inflating either count.

Step 3: Average duration over everything

AVG(dur_secs) runs across all statuses for the repo. No WHERE clause filters them out; the engineering lead wants total throughput, not just terminal builds.

The solution

SELECT repo_name,
       SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success_count,
       SUM(CASE WHEN status = 'failure' THEN 1 ELSE 0 END) AS failure_count,
       AVG(dur_secs) AS avg_duration
FROM ci_builds
GROUP BY repo_name
;

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you add a success rate column? (Divide `success_count` by `COUNT(*)` or by `(success_count + failure_count)` depending on whether in-flight builds belong in the denominator; cast to a decimal to avoid integer division.)
• What if the lead only cares about the main branch? (Add `WHERE branch = 'main'` before the GROUP BY; the conditional aggregation pattern stays unchanged and still handles every status.)
• How would you flag repos where the failure count tripled week over week? (Add `DATE_TRUNC('week', built_at)` to the grouping, then `LAG(failure_count)` partitioned by repo ordered by week and filter where the ratio exceeds three.)