First Build per Repository
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
- ESPN
Problem
The CI/CD team is mapping out when each repository first started running builds. Show each repo alongside its earliest build date.
Every repo had a first build.
Tables
ci_builds
Columns: build_id INTEGER, repo_name TEXT, branch TEXT, status TEXT, dur_secs INTEGER, trigger TEXT, built_at TEXT
| build_id | repo_name | branch | status | dur_secs | trigger | built_at |
|---|---|---|---|---|---|---|
| 5047 | backend-api | develop | failed | 34 | pull_request | 2026-02-02 01:13:00 |
| 5094 | infra | feature/auth | canceled | 53 | schedule | 2026-03-03 02:26:00 |
| 5141 | ml-pipeline | fix/login-bug | running | 72 | manual | 2026-04-04 03:39:00 |
| 5188 | mobile-app | release/v2.0 | queued | 91 | tag | 2026-05-05 04:52:00 |
| 5235 | data-platform | hotfix/payment | success | 110 | webhook | 2026-06-06 05:05:00 |
| 5282 | frontend | feature /spaces | failed | NULL | push | 2026-07-07 06:18:00 |
| 5329 | backend-api | main | canceled | 148 | pull_request | 2026-08-08 07:31:00 |
| 5376 | infra | develop | running | 167 | schedule | 2026-09-09 08:44:00 |
| 5423 | ml-pipeline | feature/auth | queued | 186 | manual | 2026-01-10 09:57:00 |
| 5470 | mobile-app | fix/login-bug | success | 205 | tag | 2026-02-11 10:10:00 |
Expected output
| repo_name | first_build_date |
|---|---|
| backend-api | 2022-01-02 01:13:00 |
| data-platform | 2022-05-14 17:53:00 |
| frontend | 2022-06-07 06:18:00 |
| infra | 2022-02-03 02:38:00 |
| ml-pipeline | 2022-03-24 03:03:00 |
Worked solution and explanation
Why this problem exists in real interviews
Interviewers use the ci_builds table here to probe row numbering within partitions combined with nested subqueries. The columns repo_name, branch, status force candidates to reason about the correct grain before writing any aggregation.
Break down the requirements
Step 1: Partition by repo_name
Group all build rows by repo_name so each repository becomes one output row.
Step 2: Filter to rank 1
Take MIN(built_at) per group to find each repository's earliest build date.
The solution
SELECT repo_name, MIN(built_at) AS first_build_date FROM ci_builds GROUP BY repo_nameCommon follow-up questions
- The `dur_secs` column in `ci_builds` has roughly 2% NULLs. How does your query handle those rows, and would the result change if NULLs were replaced with zeros? (Tests whether the candidate understands how NULLs propagate through aggregation functions and whether their WHERE/JOIN conditions implicitly filter them out.)
- Your window function uses a default frame. What is the implicit frame, and would switching to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW change anything? (Tests knowledge of default window frames (RANGE vs ROWS) and when the distinction matters.)
- `build_id` in `ci_builds` has ~2M distinct values. What index strategy keeps your query from doing a full table scan? (Tests whether the candidate can design indexes for high-cardinality columns and understands selectivity.)
- If `build_id` in `ci_builds` contained negative values, would your query still produce correct results? (Tests whether the candidate validated assumptions about the domain of numeric columns.)