The One That Flew

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

Surface the date and duration of the shortest completed build in ci_builds, the record the CI/CD team is adopting as its performance baseline, keeping in mind that a build only has a duration once it actually finishes. When several builds share that shortest time, the earliest of them sets the baseline.

The fastest build ever. When did it happen?

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 tests

Underneath the 'performance baseline' framing this is an argmin: you need the whole row that owns the smallest duration, not just the smallest number. Two things bite. First, dur_secs is NULL for builds that never finished, and the status label will not tell you which those are: a 'failed' build often carries a real duration, so the duration itself is the only honest signal. In SQLite NULL sorts as the smallest value, so ORDER BY dur_secs ASC puts an unfinished build right at the top. Second, the shortest time on this data is held by more than one build, so without a second sort key the record date is whichever tied row the engine happens to hand back. Anyone can write the sort; the separation is the NULL guard plus a tie rule that lands on the earliest built_at.

Getting there

Step 1: Drop the builds that never finished

A NULL dur_secs is not a fast build, it is an incomplete one. Restrict to dur_secs IS NOT NULL first, otherwise the smallest-sorts-first behavior of NULL hands you an unfinished build as the record.

Step 2: Order by duration, then break the tie

Sort the survivors by dur_secs ASC and take the top row with LIMIT 1. Add built_at ASC as a second sort key so that when two builds tie on duration, the earliest one is returned every time instead of an arbitrary row.

The solution

SELECT built_at, dur_secs AS min_duration
FROM ci_builds
WHERE dur_secs IS NOT NULL
ORDER BY dur_secs ASC, built_at ASC
LIMIT 1;

Common follow-up questions

• The `dur_secs` column has roughly 2% NULLs. What happens to your result if you drop the `WHERE dur_secs IS NOT NULL` clause, and why? (Tests whether the candidate understands why the NULL filter is load-bearing and how it interacts with sort order.)
• If two builds share the exact same shortest duration, how does your query decide which one to return, and is that stable across runs? (Tests awareness that ORDER BY on a non-unique column is non-deterministic without a tiebreaker.)
• On a 3M-row table, what index would let this query return the fastest build without scanning and sorting everything? (Tests whether the candidate can design an index to avoid a full sort for a top-N query.)
• How would you change this to return the fastest completed build for each `repo_name` rather than one global fastest build? (Tests extension of the pattern from a single global minimum to a per-group minimum.)