Successful Build Duration by Repository

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

Asked in

Asana

Problem

List how many seconds each repository's successful builds ran in total during January 2026, reading the ci_builds log. We want every repository in that table on the list, with a total of zero for any that has nothing to add up that month.

CI throughput, 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

This is an outer-join roster problem dressed up as a CI cost report. Everyone writes the sum. What separates candidates is where the success and January conditions live. Put them in WHERE and they run after the repositories are already gone. On this seed that query returns four rows: 'data-platform' and 'infra' vanish because neither had a successful January build, and 'frontend' comes back NULL because its only January success has no recorded dur_secs. The answer is all six repositories, three of them at zero.

Why the obvious query comes up short

SELECT repo_name,
       SUM(dur_secs) AS total_dur_secs
FROM ci_builds
WHERE status = 'success'
  AND built_at BETWEEN '2026-01-01 00:00:00' AND '2026-01-31 23:59:59'
GROUP BY repo_name

Step 1: Build the roster before you filter anything

SELECT DISTINCT repo_name FROM ci_builds yields the six repositories regardless of status or month, and that is the list the output owes. The DISTINCT is load-bearing: each repository sits on about 33 rows, so without it every matched build is joined and summed once per copy.

Step 2: Attach only January successes, in the ON clause

LEFT JOIN the roster back to ci_builds and put the repository match, status = 'success' and the date range in ON. A repository with no matching build still survives, with NULL build columns. built_at carries a time, so the upper bound is '2026-01-31 23:59:59': ending at '2026-01-31' would stop at midnight and lose the last day.

Step 3: Default the empty sum to zero

SUM over nothing but NULLs is NULL. 'data-platform' and 'infra' joined no build. 'frontend' joined one build whose dur_secs is NULL. COALESCE(SUM(cb.dur_secs), 0) turns all three into the zero the report promises.

SELECT repos.repo_name,
       COALESCE(SUM(cb.dur_secs), 0) AS total_dur_secs
FROM (SELECT DISTINCT repo_name FROM ci_builds) AS repos
LEFT JOIN ci_builds AS cb
  ON cb.repo_name = repos.repo_name
 AND cb.status = 'success'
 AND cb.built_at BETWEEN '2026-01-01 00:00:00' AND '2026-01-31 23:59:59'
GROUP BY repos.repo_name

Common follow-up questions

• If you used `SUM(CASE WHEN status = 'success' THEN dur_secs END)` grouped over all of `ci_builds` instead of a join, which repositories come back `NULL`, and why is that version a single scan? (Tests that the conditional form keeps every group by construction but still needs the zero default.)
• How would you restrict to one calendar month when `built_at` is stored as TEXT in mixed formats? (Tests awareness that string comparison only works on a consistent, zero-padded format.)
• If two builds for the same repository overlap in time, does summing `dur_secs` overstate the wall-clock time the repository kept a runner busy? (Tests the difference between summed work and elapsed time for parallel builds.)