The Merge Counter

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

L4

Asked in

ESPN

Problem

Each row of ci_builds is one CI run, and any run whose trigger is something other than 'manual' counts as a merge build. For 2026, put the three long-lived branches main, develop and release side by side: how many merge builds each had, expressed both as a whole number and as a decimal, plus their average build duration, with a branch that had no merge builds still shown at zero.

How many builds came from merges?

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

This is a fixed-row report dressed up as a branch comparison. The report owes you exactly three rows, main, develop and release, and the seed has no build on a branch named exactly 'release' at all. Anyone can filter to merge builds and count them. What separates candidates is noticing that the row list comes from the question, not from the data. Let the data decide which rows exist and 'release' quietly disappears, so the comparison ships with a hole where its zero should be.

Grouping by branch

GROUP BY branch only creates a group for a value that survives the WHERE. No row has branch = 'release', so the result has two rows, main and develop, and the third branch is simply gone.

One aggregate per named branch

An aggregate with no grouping always returns exactly one row. Over zero rows that row is COUNT(*) = 0 and AVG(dur_secs) is NULL, so 'release' shows up at zero, labelled by the literal you gave it.

Step 1: Define a merge build once

A merge build is any run whose trigger is not 'manual'. trigger has no NULLs in ci_builds, so trigger != 'manual' is the whole rule here. On a feed where it could be NULL you would decide on purpose whether those runs count, because NULL != 'manual' is not true and drops the row.

Step 2: Scope to 2026 by when the run happened

built_at spans five years. strftime('%Y', built_at) = '2026' keeps only this year's runs. Leave it out and main jumps from 14 merge builds to 24, which is the kind of quiet inflation nobody catches in review.

Step 3: Name each branch as a literal

Each branch gets its own SELECT whose WHERE ends in branch = '...' and whose output column is the literal name, not the branch column. That literal is what lets the empty 'release' select still label its row: there is no branch value to read, only the zero-row aggregate. Exact equality also keeps 'release/v2.0' out of 'release'.

Step 4: Stitch the three rows together

Each select returns exactly one row, so combining the three with UNION gives exactly three. The literal names differ, so the duplicate check never removes anything; UNION ALL would return the same rows and skip that check.

The solution

SELECT 'main' AS branch,
       COUNT(*) AS build_count,
       CAST(COUNT(*) AS REAL) AS build_count_decimal,
       AVG(dur_secs) AS avg_duration
FROM ci_builds
WHERE trigger != 'manual'
  AND strftime('%Y', built_at) = '2026'
  AND branch = 'main'
UNION
SELECT 'develop' AS branch,
       COUNT(*) AS build_count,
       CAST(COUNT(*) AS REAL) AS build_count_decimal,
       AVG(dur_secs) AS avg_duration
FROM ci_builds
WHERE trigger != 'manual'
  AND strftime('%Y', built_at) = '2026'
  AND branch = 'develop'
UNION
SELECT 'release' AS branch,
       COUNT(*) AS build_count,
       CAST(COUNT(*) AS REAL) AS build_count_decimal,
       AVG(dur_secs) AS avg_duration
FROM ci_builds
WHERE trigger != 'manual'
  AND strftime('%Y', built_at) = '2026'
  AND branch = 'release'

SELECT branch,
       COUNT(*) AS build_count,
       CAST(COUNT(*) AS REAL) AS build_count_decimal,
       AVG(dur_secs) AS avg_duration
FROM ci_builds
WHERE trigger != 'manual'
  AND strftime('%Y', built_at) = '2026'
  AND branch IN ('main', 'develop', 'release')
GROUP BY branch

Common follow-up questions

• The team adds a fourth branch, `staging`. What changes, and how do you avoid copying a whole `SELECT` again? (Pushes toward a list of branch names joined to a single aggregate, which keeps the zero rows without repeating the filter.)
• `strftime('%Y', built_at)` wraps the column. How would you write the 2026 filter so a partitioned table can prune? (A range on the raw column, `built_at >= '2026-01-01' AND built_at < '2027-01-01'`, selects the same runs and lets the engine skip partitions.)
• What if `trigger` could be NULL? Is that run a merge build? (`NULL != 'manual'` is not true, so the run drops out unless you add `trigger IS NULL` deliberately.)
• Should 'release/v2.0' roll up into 'release'? (Tests whether you ask instead of switching to a prefix match: `LIKE 'release%'` would change the answer for the release row.)