The Slow Creep

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

L3

Asked in

ESPN

Problem

Our CI builds feel like they have been creeping slower year after year, and we want to see where that is happening repository by repository. From ci_builds, give the average build duration for each repository and calendar year, unrounded and in seconds, slowest average first.

Every year the builds got heavier. Find where the minutes went.

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

Under the CI-metrics costume, this is a two-dimensional grouping problem where one of the two dimensions does not exist yet. Anyone can group by repo and take AVG(dur_secs). What separates candidates is that the year is not stored anywhere: it has to be derived from built_at, a TEXT timestamp. Group by the raw string and every build becomes its own group. Leave the year out and you get one number per repo, which hides the creep entirely. The quieter second test is the missing durations. How your average treats the builds with no dur_secs decides whether every figure is right or quietly too low.

Break down the requirements

Step 1: Pull the year from a text timestamp

built_at is stored as TEXT, not a native timestamp, so derive the year from it: STRFTIME('%Y', built_at) returns the four-digit year. Whatever date-part expression you choose, use the same one in the select list and the GROUP BY, so the label you show is the bucket you grouped on.

Step 2: Average duration per repo per year

Group on the pair (repo_name, derived year) and take AVG(dur_secs). Grouping on both dimensions is the whole point: drop either one and you have answered a different question.

Step 3: Sort slowest first, keep it unrounded

Order by avg_duration DESC so the slowest repository-years sit on top. That is the whole ordering the question asks for: two equal averages may come back in either order and both are correct. Report the average exactly as AVG returns it, in seconds. Rounding it to tidy the output changes the number being graded.

The solution

SELECT
    repo_name,
    STRFTIME('%Y', built_at) AS build_year,
    AVG(dur_secs) AS avg_duration
FROM ci_builds
GROUP BY repo_name, STRFTIME('%Y', built_at)
ORDER BY avg_duration DESC

Common follow-up questions

• How would you report the P95 build duration alongside the average? (Tests whether they reach for `PERCENTILE_CONT(0.95)` and know which engines support it, or fall back to a ranking workaround.)
• What if you only wanted the repos that got slower year over year? (Tests window functions: `LAG(avg_duration)` partitioned by `repo_name`, ordered by `build_year`, then compare consecutive years.)
• How would you restrict this to only successful builds? (Tests where the filter belongs: a `WHERE status = 'success'` before grouping, not a `HAVING` after.)