Build Health

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 building a CI health report for the all-hands that scores each repository in ci_builds by how often its builds pass. Compute that pass rate over every build a repo ran, not just the ones that ended in success or failure, and include a repository only once at least 13 of its builds have resolved into a success or a failure.

Score every repo by how often CI stays green.

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 is an enum with more than two values. 'success' and 'failed' are the ones you report, but the rate is over COUNT(*), so canceled, running, and queued builds all count against the repo. The twist: the report only lists repos with enough *resolved* builds to trust the number, and that resolved count is a different measure from the denominator.

Break down the requirements

Step 1: Pivot status into columns

Two SUM(CASE WHEN status = ? THEN 1 ELSE 0 END) expressions for the resolved counts plus a COUNT(*) for total_builds, all in one GROUP BY repo_name pass.

Step 2: Rate is over all builds

Divide successes by COUNT(*), not by success + failed. Canceled, running, and queued builds belong in the denominator. Scale by 100.0 first to force float division, then wrap the result in ROUND(..., 3).

Step 3: Gate on resolved volume

HAVING SUM(CASE WHEN status IN ('success','failed') THEN 1 ELSE 0 END) >= 13 keeps only repos with enough finished builds for the rate to mean anything. Notice this gate counts *resolved* builds while the rate's denominator counts *all* of them: two different measures living in one query.

The solution

SELECT
  repo_name,
  SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success_count,
  SUM(CASE WHEN status = 'failed'  THEN 1 ELSE 0 END) AS failed_count,
  COUNT(*) AS total_builds,
  ROUND(100.0 * SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) / COUNT(*), 3) AS success_rate
FROM ci_builds
GROUP BY repo_name
HAVING SUM(CASE WHEN status IN ('success', 'failed') THEN 1 ELSE 0 END) >= 13

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you scope this to the last 30 days? (Add `WHERE built_at >= date('now','-30 days')`. Partition pruning on `built_at` shrinks the scan from lifetime to a single month.)
• What if a new `status` value gets introduced upstream? (A new value silently lowers every repo's rate without warning. Audit `DISTINCT status` on each run, or normalize through a staging view that maps unknowns to a known bucket.)
• How would you surface only the least healthy repos? (Add a second predicate to the `HAVING`, `ROUND(100.0 * SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) / COUNT(*), 3) < 20`, to keep only repos below the target rate for triage.)