Twice Over

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

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.

Same model, trained twice.

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 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

SELECT mdl_name, COUNT(*) AS run_count
FROM ml_models
WHERE status <> 'failed'
GROUP BY mdl_name
HAVING COUNT(*) > 20

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.)