# The One That Flew > The fastest build ever. When did it happen? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem The CI/CD team is setting a performance baseline from the fastest build ever recorded, and a build only has a duration once it actually finishes. Surface the date and duration of the shortest completed CI build on record, and if more than one build ties for the shortest, take the earliest one. ## Worked solution and explanation ### What this really tests Underneath the 'performance baseline' framing this is a minimum-with-a-tiebreaker, and the whole thing turns on one property you can't see from the prompt: `dur_secs` is nullable. Builds that failed, were canceled, or are still running or queued never recorded a duration, so they sit in the table as NULLs. Here is the bite: in SQLite NULL sorts as the smallest value, so `ORDER BY dur_secs ASC` puts a NULL-duration build right at the top. Skip the filter and your 'fastest build ever' is a build that never actually finished. Anyone can write the sort; the separation is whether you exclude the NULLs and add a tiebreaker so the record does not flip between runs. --- ### 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 **Fastest completed build** ```sql 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; ``` > **Cost Analysis** > > On 3M rows the naive plan is a full scan plus a sort, but the LIMIT 1 means the engine only needs the top of the ordering. A composite index on (dur_secs, built_at) turns this into a top-1 lookup that reads a single index entry instead of sorting the whole table. > **Interviewers Watch For** > > The interviewer is watching whether you notice the nullable duration and exclude it, and whether you order by dur_secs ascending to reach the minimum. Ordering only by duration with no plan for which built_at wins a tie is the tell of someone who has not thought the problem through. > **Common Pitfall** > > Omitting the NULL filter lets an unfinished build win, because NULL sorts first under ASC. The other trap is sorting only by dur_secs with no tiebreaker: with two builds tied on duration, the returned row is non-deterministic across runs. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_one_that_flew) - [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.