Average Build Duration by Repo

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

Delta Air Lines

Problem

Slow builds are blocking deploys, and the CI/CD team suspects a few repos are dragging down the pipeline. From the build log in ci_builds, show the average build duration in seconds for each repository so the team can spot the worst offenders.

Some repos build fast. Others don't.

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 per-repo mean over a column with holes in it, dressed up as a CI slowness hunt. Anyone can write GROUP BY repo_name with AVG(dur_secs). What separates candidates is whether they trust how AVG handles missing values or try to 'fix' it. AVG leaves a missing duration out of both the sum and the count, and a repo with no recorded durations comes back with a blank average. Hand-roll SUM(dur_secs) / COUNT(*) or wrap the column in COALESCE(dur_secs, 0) and you treat a duration nobody captured as a real build. On this data that crowns 'frontend' the fastest repo in the company at 0 seconds.

Step 1: Set the grain at repo_name

One row per repository is the whole ask. branch and trigger are tempting extra columns, but each one splits a repo's average into pieces nobody asked for. A single global AVG(dur_secs) is the opposite mistake: one number and no repo to blame.

Step 2: Let AVG skip the missing durations

dur_secs is NULL on 32 of the 200 builds, and every one of them belongs to 'frontend'. AVG divides by the count of non-NULL values, so the other five repos average only what was measured, and 'frontend' returns NULL instead of an invented number. That blank row is the honest answer: the repo exists, but its durations were never captured.

Step 3: Keep every status in

status mixes 'success', 'failed', 'canceled', 'running' and 'queued' builds. The ask covers every build per repo, so there is no status filter. Keeping only successes quietly shifts every average in the result. If you think outcome matters, raise it as a question rather than adding a silent WHERE.

The solution

SELECT repo_name, AVG(dur_secs) AS avg_duration
FROM ci_builds
GROUP BY repo_name

Hand-rolled mean

SUM(dur_secs) / COUNT(*) puts rows with no duration in the denominator but not the numerator. On an INTEGER column most engines also divide as integers, so 'backend-api' reads 416 instead of 416.588. COALESCE(dur_secs, 0) is worse: 'frontend' becomes a 0-second repo.

Built-in average

AVG(dur_secs) excludes NULLs from both sides, returns a fractional mean, and leaves 'frontend' NULL. It is the same single pass with nothing to get wrong.

Where the interviewer goes next

Common follow-up questions

• How would you report the 95th percentile build duration per repo instead of the mean? (Tests whether you reach for `PERCENTILE_CONT` or `APPROX_PERCENTILE` and understand why p95 matters for CI tail latency.)
• How would you split the average by `status` so we can compare successful vs failed build times? (Probes conditional aggregation with `AVG(CASE WHEN status = 'success' THEN dur_secs END)`.)
• How would you flag repos whose average duration jumped week over week? (Tests a weekly bucket on `built_at` with `DATE_TRUNC` and `LAG` for the week-over-week delta.)