Builds per Author per Branch

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

Problem

In our ci_builds log, how many builds ran for each pairing of trigger and branch? We read the list alphabetically by trigger, then by branch.

Who triggered what, and where?

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 two-key count dressed up as a release report. The grain is one row per (trigger, branch) pairing, and everyone who says that out loud gets it right. The candidates who slip only say half of it: they key the count on trigger alone, get 6 rows of 32 to 34 builds each, and hand the release manager totals with every branch merged together. No sort can recover a breakdown the count has already thrown away.

Build it in four decisions

Step 1: Name the grain before you type

One row per (trigger, branch). Every trigger on this seed fires on all 7 branches, so expect 42 rows. Saying the row count first is the cheapest check you can run against your own query.

Step 2: Count rows, not a column

Build volume is COUNT(*). build_id is never null, so COUNT(build_id) gives the same result, but COUNT(*) says 'rows in this bucket' without making the reader check the column. Leave status alone: a 'queued' or 'running' build is still a build.

Step 3: Emit the trigger as author

The expected output labels the trigger column author, so select trigger AS author. Keep branch exactly as stored, 'feature /spaces' included: it is a real branch, and tidying it would emit a key that no build carries.

Step 4: Sort by the two keys

ORDER BY author, branch. Each pairing is a single row, so those two keys already fix the sequence. A third key would never be consulted, and writing one suggests you have not checked the grain.

SELECT trigger AS author, branch, COUNT(*) AS build_count
FROM ci_builds
GROUP BY trigger, branch
ORDER BY author, branch

Keyed on trigger only

GROUP BY trigger returns 6 rows of 32 to 34 builds. Each number is correct, but it answers a question nobody asked, and branch can no longer be selected meaningfully.

Keyed on trigger and branch

GROUP BY trigger, branch returns 42 rows of 4 or 6 builds, one per pairing, which is the report the release manager reads line by line.

Follow-up questions

Common follow-up questions

• Reshape the output so each author is one row with a column per branch. (Tests conditional aggregation with `SUM(CASE WHEN branch = ... THEN 1 END)` and naming the pivot keys statically.)
• Only show (author, branch) pairs with at least 50 builds. (Tests `HAVING` versus `WHERE` and that aggregate filters belong after the `GROUP BY`.)
• Add the failure rate per (author, branch). (Tests conditional aggregation: `AVG(CASE WHEN status = 'failed' THEN 1.0 ELSE 0 END)` in the same grouped projection.)
• Restrict to the last 30 days of builds. (Tests pushing a `WHERE built_at >= ...` predicate before the aggregate so partition pruning fires on `built_at`.)