# Twice Over > Same model, trained twice. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 · Asked in: General Assembly ## Problem Every training run our ML platform kicks off lands as a row in ml_models, and the registry's health check expects no model to hold more than 20 runs once failed attempts are set aside, since anything above that usually means runs are being registered twice over. Report the models that break that expectation, along with how many runs each one racked up. ## 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 4 of 4 rows: | mdl_name | run_count | | --- | --- | | churn_pred | 26 | | demand_fcst | 24 | | fraud_detect | 24 | | img_classify | 26 | ## Worked solution and explanation ### What this problem is really about Underneath the registry health check this is a two-stage filter, and the whole test is knowing which stage each condition lives in. The expectation is a cap: no model should hold more than twenty live runs, and the report is the models that break it. Dropping the failed runs happens row by row, before anything is grouped, so it belongs in `WHERE`. Testing the cap happens after the rows collapse into per-model totals, so it can only live in `HAVING`. Swap them and it breaks: write `WHERE COUNT(*) > 20` and the query errors, because the count does not exist yet when `WHERE` runs. Skip the failed-run filter and all eight models fail the check. The subtler miss is `COUNT(DISTINCT version)`, which erases the very repeats the check exists to catch and reports nothing. --- ### Working through it #### Step 1: Drop the failed runs first `WHERE status <> 'failed'` removes the runs that never produced a model, row by row, before any grouping happens. This is a row-level test, so it sits in `WHERE`. Leave it out and failed attempts pad every model's total, so models that never really trained that often get flagged as violators. #### Step 2: Group by the model, count every run Each row in `ml_models` is one training run, so a model registered over and over shows up as many rows sharing one `mdl_name`. `GROUP BY mdl_name` with `COUNT(*)` counts those runs. Reaching for `COUNT(DISTINCT version)` collapses the exact repeats you are hunting for. #### Step 3: Test the cap with HAVING `HAVING COUNT(*) > 20` is the expectation itself: it keeps only the models over the cap, which is exactly the list the health check reports. That count does not exist until after `GROUP BY`, so the condition cannot sit in `WHERE`. --- ### The solution **Models over the twenty-run cap** ```sql SELECT mdl_name, COUNT(*) AS run_count FROM ml_models WHERE status <> 'failed' GROUP BY mdl_name HAVING COUNT(*) > 20 ``` > **Report the breaches, not the census** > > A health check reports what fails, not everything it looked at. The query returns only the four violators, so an empty result means the registry is clean and an alert can fire on any row at all. > **Single pass, no join** > > One table, a cheap predicate on `status`, then a hash aggregation over the low-cardinality `mdl_name`. No join, no subquery, a single scan; this stays cheap even at millions of rows. > **Where the count condition goes** > > The tell is whether the cap lands in `HAVING` the moment it references an aggregate. A candidate who writes `WHERE COUNT(*) > 20` has not internalized that `WHERE` runs before the grouping, and interviewers catch it instantly. > **Distinct versions and failed runs** > > Two ways to get the numbers wrong: `COUNT(DISTINCT version)` erases the repeats that ARE the signal, and forgetting to drop the failed runs inflates the totals so the wrong models get flagged. Count every non-failed row. --- ## Common follow-up questions - What if the cap only applied to runs from the last 30 days? _(Tests moving a row filter onto `train_at` with a rolling date window.)_ - How would you keep only the highest-accuracy run per model? _(Tests `ROW_NUMBER` with PARTITION BY `mdl_name` ORDER BY `accuracy` DESC.)_ - How would you stop duplicate runs from landing in the table in the first place? _(Tests unique constraints or application-level deduplication at write time.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/twice_over) - [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.