The Runs That Never Ended

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

eBay

Problem

For each model in the ML registry, calculate the average accuracy and the completion rate (percentage of training runs where accuracy is not null). Return the model name, average accuracy, and completion rate.

Some models learn. Some just stop answering. Find out which ones finish what they start.

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

This is a data-completeness audit dressed up as model evaluation. One column, accuracy, has to answer two questions: how good is a model when a run produced a score, and how often did a run produce one at all. Anyone writes GROUP BY mdl_name with AVG(accuracy). What separates candidates is treating NULL as a run that never finished, not a run that scored zero. You let the aggregates' NULL-skipping do the work: AVG and COUNT(accuracy) ignore NULLs, and COUNT(*) does not. Put COUNT(*) in the numerator and 'fraud_detect', whose every run is NULL, reports 100% complete. The one model this report exists to flag comes out looking perfect.

One column, two statistics

Step 1: Group to one row per mdl_name

model_id identifies a training run, not a model. 'rec_engine' shows up under several versions and ids. The output grain is the model name, so GROUP BY mdl_name and nothing else. Adding version would split each model into fragments.

Step 2: Let AVG(accuracy) skip the NULLs

AVG divides the sum of non-null values by the count of non-null values. That is exactly the mean over finished runs. Don't wrap the column in COALESCE(accuracy, 0): that invents zero-accuracy runs and drags down the average of every partially-null model.

Step 3: Divide COUNT(accuracy) by COUNT(*)

Same column, two counts. COUNT(accuracy) counts only runs with a score, so it is the numerator. COUNT(*) counts every run, so it is the denominator. Multiply by 100.0 before dividing so the ratio is computed in floating point.

Step 4: Round each metric last

Apply ROUND(..., 4) to the average and ROUND(..., 2) to the percentage, wrapping the finished expressions. If you round an intermediate value, you bake the error into the division.

SELECT mdl_name,
    ROUND(AVG(accuracy), 4) AS avg_accuracy,
    ROUND(COUNT(accuracy) * 100.0 / COUNT(*), 2) AS completion_rate
FROM ml_models
GROUP BY mdl_name

What candidates write

COUNT(*) * 100 / COUNT(*) is 100 for every model. AVG(COALESCE(accuracy, 0)) counts missing runs as zero-accuracy runs. 'fraud_detect' comes out at 100% complete with an average of 0.

What the data needs

COUNT(accuracy) * 100.0 / COUNT(*) measures finished runs against all runs. AVG(accuracy) averages only real scores. 'fraud_detect' comes out at 0.0 complete with a NULL average, which is the honest answer.

Where it breaks

Common follow-up questions

• Should a run with `status` 'failed' but a non-null `accuracy` count as complete? (Tests whether you question the proxy: non-null `accuracy` stands in for completion, but `status` may disagree with it.)
• `status` mixes 'Deployed' and 'deployed'. How would you add a completion rate per status? (Tests normalizing with `LOWER(status)` before grouping instead of trusting raw values.)
• Rank models by `completion_rate`, breaking ties by `avg_accuracy`. Where do NULL averages go? (Tests `ORDER BY ... DESC NULLS LAST` and awareness that engines sort NULL differently by default.)