# The Runs That Never Ended > Some models learn. Some just stop answering. Find out which ones finish what they start. Canonical URL: 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. ## Tables ### ml_models - model_id INTEGER - mdl_name TEXT - version TEXT - accuracy REAL - status TEXT - train_at TEXT - framework TEXT Sample rows (10 of 200): | model_id | mdl_name | version | accuracy | status | train_at | framework | | --- | --- | --- | --- | --- | --- | --- | | 123 | rec_engine | v1.1 | 0.72 | training | 2026-02-02 01:00:00 | TensorFlow | | 146 | churn_pred | v2.0 | 0.89 | archived | 2026-03-03 02:00:00 | XGBoost | | 169 | spam_filter | 3.0 | 0.61 | failed | 2026-04-04 03:00:00 | sklearn | | 192 | img_classify | v2.1-beta | 0.78 | validating | 2026-05-05 04:00:00 | pytorch | | 215 | sentiment | 1.0.0 | 0.95 | Deployed | 2026-06-06 05:00:00 | tensorflow | | 238 | demand_fcst | v1.0 | 0.67 | deployed | NULL | PyTorch | | 261 | price_opt | v1.1 | 0.84 | Training | 2026-08-08 07:00:00 | TensorFlow | | 284 | fraud_detect | v2.0 | NULL | archived | 2026-09-09 08:00:00 | XGBoost | | 307 | rec_engine | 3.0 | 0.73 | failed | 2026-01-10 09:00:00 | sklearn | | 330 | churn_pred | v2.1-beta | 0.9 | validating | 2026-02-11 10:00:00 | pytorch | ## Expected output First 5 of 8 rows: | mdl_name | avg_accuracy | completion_rate | | --- | --- | --- | | churn_pred | 0.8808 | 100 | | demand_fcst | 0.725 | 100 | | fraud_detect | NULL | 0 | | img_classify | 0.84 | 100 | | price_opt | 0.895 | 100 | ## 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. **`COUNT(accuracy)` vs `COUNT(*)` in one pass** ```sql 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 > **Integer division silently zeroes the rate** > > `COUNT(accuracy) / COUNT(*) * 100` divides two integers first. In SQLite and Postgres, 9 / 10 is 0, so every partially-null model reports 0 and only fully complete models survive. Leading with `* 100.0` promotes the whole expression to a float before the division happens. > **An all-NULL group still earns a row** > > The '`fraud_detect`' rows exist, so `GROUP BY` emits the group. `AVG` over zero non-null values returns NULL, and `COUNT(accuracy)` returns 0. If you add `WHERE accuracy IS NOT NULL` to make the math feel safe, you delete the model from the report entirely. > **Define NULL out loud before typing** > > The senior tell is saying "a NULL `accuracy` means the run produced no score, so it counts against completion and stays out of the mean" before writing any SQL. Then say that the average for an all-NULL model stays NULL, not 0. That shows you chose the semantics on purpose rather than inheriting them. > **Three aggregates, one scan** > > All three aggregates run in a single hash aggregate over 2,500 rows into about 100 groups. There are no joins and no subqueries. At 100M rows it is still one sequential pass. An index on `mdl_name` rarely beats the scan, because every row is read anyway. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_runs_that_never_ended) - [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.