# The Slow Creep

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

Canonical URL: <https://datadriven.io/problems/the_slow_creep>

Domain: SQL · Difficulty: medium · Seniority: L3

## 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.

## 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

**Multi-dimension aggregation sorted by performance**

```sql
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
```

> **Where the time goes**
>
> Full scan of 3M rows, then the grouping collapses everything to roughly 60 repos times a handful of years, on the order of a couple hundred rows. The AVG and the sort run over that tiny result set, so the scan is the only real cost and there is nothing to optimize beyond it.

> **Interviewers watch for**
>
> Whether you noticed dur_secs is 2% NULL. AVG silently skips NULLs and divides by the non-null count, which is what you want for a duration average. The candidate who says this out loud, rather than discovering it by accident, reads as someone who has been burned before.

> **Common pitfall**
>
> Rolling your own average as SUM(dur_secs) / COUNT(*). With NULLs present, SUM ignores them but COUNT(*) counts every row, so you divide by too large a denominator and report an artificially low average. Either use AVG, or be deliberate with COUNT(dur_secs).

---

## 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_slow_creep)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.