Stumbling Out of the Gate

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

L4

Asked in

DoorDash

Problem

Status in ml_models is spelled in mixed case, a run with no accuracy never produced a usable metric and so can never be a version's first run, and a version whose runs never logged a train_at still counts. Taking each model version's (mdl_name, version) earliest remaining run by train_at, the ML platform team wants the percentage of those first runs with a final outcome (deployed, failed or archived) that failed, kept as a decimal rather than cut to a whole number.

Some model versions never recover from a bad opening run.

Tables

ml_models

Columns: model_id INTEGER, mdl_name TEXT, version TEXT, accuracy REAL, status TEXT, train_at TEXT, framework TEXT

Expected output

Worked solution and explanation

What this really is

This is a first-touch question wearing an MLOps badge: pick one row per version, then compute a rate over a carefully scoped subset. Anyone can write the ROW_NUMBER(). What separates candidates is the three quiet rules around it: which runs are even eligible to be first, which first runs have a verdict to count, and whether the division keeps its fraction. Miss any one and the number moves: 40% if 'Deployed' slips out of the outcome set, 28 if the counts divide as integers, 25% if metric-less versions sneak in. The right answer is 28.571%.

Walk it in order

Step 1: Decide eligibility before choosing the first run

Put accuracy IS NOT NULL in the CTE's WHERE, so the ranking only ever sees runs that produced a metric. Filter afterward and a metric-less run can claim run_order = 1 and then vanish, taking its version with it or leaving the wrong run in place. Do not add a train_at filter: a version that never logged a timestamp still has a first run, and dropping those versions moves the answer to 36.364%.

Step 2: Normalize status once, in the CTE

LOWER(status) AS outcome means every later comparison reads one spelling. The data stores both 'deployed' and 'Deployed', and they are the same verdict.

Step 3: Keep only first runs with a final outcome

run_order = 1 AND outcome IN ('deployed', 'failed', 'archived'). A first run that is still training or validating has not stumbled or succeeded yet, so it belongs in neither the numerator nor the denominator.

Step 4: Divide as a decimal

CAST(SUM(...) AS REAL) * 100 / COUNT(*). Without the cast, two integers divide to an integer and 4 failures out of 14 reports 28, not 28.571.

WITH first_runs AS (
    SELECT
        mdl_name,
        version,
        LOWER(status) AS outcome,
        ROW_NUMBER() OVER (PARTITION BY mdl_name, version ORDER BY train_at) AS run_order
    FROM ml_models
    WHERE accuracy IS NOT NULL
)
SELECT
    CAST(SUM(CASE WHEN outcome = 'failed' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(*) AS pct_failed_first_run
FROM first_runs
WHERE run_order = 1
  AND outcome IN ('deployed', 'failed', 'archived')

Integer division

SUM(...) * 100 / COUNT(*) with two integer operands: 400 / 14 is 28.

Decimal division

CAST(SUM(...) AS REAL) * 100 / COUNT(*): 28.571. Casting one operand is enough to make the whole expression decimal.

Common follow-up questions

• Here a version either logged `train_at` on every run or on none. What if one version mixed timestamped and untimestamped runs? (Engines disagree on where `NULL` sorts; say whether those runs should be eligible to be first and pin it with `NULLS LAST` or a filter.)
• What if two runs of a version share the same `train_at`? (`ROW_NUMBER()` picks one arbitrarily; add `model_id` as a tiebreaker to make the first run deterministic.)
• How would you report this per model instead of overall? (Group the outer query by `mdl_name` and check the per-model rates actually differ before presenting them.)