# Stumbling Out of the Gate

> Some model versions never recover from a bad opening run.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

Every model version goes through a series of training runs. We want to know how often a version stumbles right out of the gate. For each model version (a unique combination of mdl_name and version), find its very first training run, ordered by train_at. Of those first runs, what percentage ended with a status of 'failed' (status casing is inconsistent in the data)? Only count runs that actually recorded an accuracy score, since a null accuracy means the run never produced a usable metric and should not be considered a real first run. Return a single percentage.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests combining a per-group first-row identification with conditional aggregation. Interviewers check whether you can find each model version's first training run, filter by a condition, and compute a percentage while handling inconsistent status casing.

---

### Break down the requirements

#### Step 1: Find each model version's first training run

Use `ROW_NUMBER() OVER (PARTITION BY mdl_name, version ORDER BY train_at)` to rank each version's runs chronologically. Filter to `rn = 1` for the very first run.

#### Step 2: Exclude runs with no accuracy score

`WHERE accuracy IS NOT NULL` removes runs that never produced a usable metric, so they are not treated as a real first run.

#### Step 3: Compute percentage of failed first runs

Count first runs where `LOWER(status) = 'failed'` (status casing is inconsistent) divided by total first-run rows, multiplied by 100.

---

### The solution

**First-run identification with conditional percentage**

```sql
WITH first_runs AS (
    SELECT
        mdl_name,
        version,
        status,
        ROW_NUMBER() OVER (PARTITION BY mdl_name, version ORDER BY train_at) AS rn
    FROM ml_models
    WHERE accuracy IS NOT NULL
)
SELECT
    CAST(SUM(CASE WHEN LOWER(status) = 'failed' THEN 1 ELSE 0 END) AS REAL) * 100.0 / COUNT(*) AS pct_failed_first_run
FROM first_runs
WHERE rn = 1
```

> **Cost Analysis**
>
> With 3K rows, the window function sort within each model-version partition is trivial. The entire query completes in milliseconds.

> **Interviewers Watch For**
>
> Whether the `accuracy IS NOT NULL` filter is applied before ROW_NUMBER. Runs with no recorded accuracy should not be considered the 'first' run. Filtering before ranking ensures the first metric-producing run is selected. They also watch for `LOWER(status)` to handle inconsistent casing.

> **Common Pitfall**
>
> Filtering `WHERE LOWER(status) = 'failed'` before finding the first run. This would only find versions whose failed run happens to be first, missing versions whose first run had a different status.

---

## Common follow-up questions

- What if two training runs have the same train_at timestamp for a model version? _(ROW_NUMBER is non-deterministic on ties; add another column such as a run id as a tiebreaker in the ORDER BY.)_
- How would you find which model versions recovered after a failed first run? _(Compare first run status to the most recent run status per version using window functions to see which versions recovered.)_
- What if every run for a version has NULL accuracy? _(Runs with NULL accuracy are excluded here, so they never count as a first run. Clarify the business rule for NULL accuracy if requirements change.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/stumbling_out_of_the_gate)
- [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.