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 some models get retrained far more than others. Ignoring the runs that failed outright, find the models that have been trained more than 20 times, 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 retraining story this is a two-stage filter, and the whole test is knowing which stage each condition lives in. Dropping the failed runs happens row by row, before anything is grouped, so it belongs in WHERE. Keeping only the models with more than twenty runs 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. The subtler miss is COUNT(DISTINCT version), which erases the very repeats you are counting and makes every over-trained model look like it ran once.

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, quietly pushing some models over the threshold that never really trained that often.

Step 2: Group by the model, count every run

Each row in ml_models is one training run, so a model retrained 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: Filter the aggregate with HAVING

HAVING COUNT(*) > 20 keeps only the heavily retrained models. That count does not exist until after GROUP BY, so the condition cannot sit in WHERE; filtering on an aggregate is exactly what HAVING is for.

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 we wanted the models retrained more than twenty times in just 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 redundant runs from landing in the table in the first place? (Tests unique constraints or application-level deduplication at write time.)