Build Success Rate by Trigger

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

Meta

Problem

Give us each trigger type's total number of builds in ci_builds and the fraction of them that finished with status 'success', as a decimal. We're checking whether manually triggered builds succeed less often than automated ones, so every build counts toward the total whatever its status.

Which triggers produce green builds?

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

This is a per-group ratio dressed up as a CI reliability question, and the grouping and the conditional count are the easy part. What separates candidates is the division: the success count and COUNT(*) are both integers, so most engines divide them as integers and every trigger's share comes back as 0. The query still returns six tidy rows, which is exactly why nobody notices until the dashboard claims every trigger fails every time.

Building it

Step 1: Set the grain to one row per trigger

The output compares trigger types, so GROUP BY trigger is the whole grain. Six triggers on the seed means six rows; more than that means something extra, like status, slipped into the grouping.

Step 2: Count every build, whatever its status

COUNT(*) is the denominator, and it must keep the 'running', 'queued' and 'canceled' builds. Counting only finished builds answers a different question and inflates every share.

Step 3: Count successes in the same pass

SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) turns each row into a 1 or a 0 and adds them per trigger. No second scan, no self-join, no subquery.

Step 4: Keep the division fractional

Cast the success count with CAST(... AS REAL) before dividing by COUNT(*). No trigger on this seed succeeds more than a quarter of the time, so integer division floors every share to 0.

SELECT
    trigger,
    COUNT(*) AS total_builds,
    CAST(SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS REAL)
      / COUNT(*) AS success_rate
FROM ci_builds
GROUP BY trigger

Integer division

SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) / COUNT(*) gives 0 for every trigger: 6 / 34 truncates to 0, and so does 8 / 32.

Fractional division

CAST(... AS REAL) / COUNT(*) keeps the fraction: 'manual' lands at 0.176 and 'webhook' at 0.25, which is the comparison the question exists for.

Common follow-up questions

• How would you return `success_rate` and `failure_rate` side by side per trigger in one query? (Extends the conditional count to several statuses in one pass.)
• What if the team only wanted triggers with at least 1,000 builds? Where does that filter go? (Tests filtering on an aggregate rather than on rows.)
• If one trigger has only 3 builds, its share can only be 0, 0.33, 0.67 or 1. How would you keep small samples from dominating the comparison? (Tests awareness of unstable rates and smoothing.)