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

The platform team is checking whether CI builds have crept slower across the years in each repository. Give the average build duration per repository and calendar year, slowest average first, breaking ties by repository name alphabetically and then from the earliest year.

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 with a sorting twist. Anyone can group by repo and pull AVG(dur_secs); what actually separates candidates is two quieter things. First, prying the calendar year out of a TEXT timestamp without reaching for EXTRACT, which the SQLite sandbox rejects outright. Second, making the slowest-first ordering deterministic. Skip the tie-breaker and your rows reshuffle whenever two averages collide, and a grader that diffs exact row order fails a query that is arithmetically perfect.

Break down the requirements

Step 1: Pull the year from a text timestamp

built_at is stored as TEXT, not a native timestamp, so reach for STRFTIME('%Y', built_at) to get the four-digit year. This is the SQLite move; EXTRACT(YEAR FROM ...) is the Postgres habit that crashes the sandbox.

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 and you have answered a different question.

Step 3: Sort slowest to fastest, deterministically

Order by avg_duration DESC to put the slowest averages on top, then add repo_name and build_year as tie-breakers so equal averages always land in the same order across runs.

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, repo_name, build_year

Common follow-up questions

• How would you report the P95 build duration alongside the average? (Tests whether they reach for `PERCENTILE_CONT` or, lacking it in SQLite, an NTILE or self-rank workaround.)
• What if you only wanted the repos that got slower year over year? (Tests window functions: LAG over `avg_duration` partitioned by repo, ordered by 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.)