Models With Variable Accuracy

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

City and County of San Francisco

Problem

Show each model's lowest and highest accuracy in the ml_models registry, alphabetically by model name. Only the models whose accuracy actually moved between versions belong in the list.

Accuracy should be stable. These models are not.

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 group filter dressed up as a registry audit. Whether a model belongs in the answer depends on two numbers that only exist after its versions are collapsed: its lowest and highest accuracy. Anyone can write MIN and MAX per mdl_name. What separates candidates is where the 'they differ' test lives. No single row knows its model's low or high, so the test has to run on the collapsed groups. Skip it and you ship 'fraud_detect', a model with no measured accuracy on any version, as an eighth row with an empty low and high.

Walk it through

Step 1: Collapse versions to one row per mdl_name

ml_models holds one row per version, so 'per model' means GROUP BY mdl_name, not model_id. model_id is unique per row, so grouping by it would give every version its own group where the low always equals the high. MIN(accuracy) and MAX(accuracy) then give each model its range.

Step 2: Test the range after collapsing

The condition compares two aggregates of the same group, so it belongs in HAVING MIN(accuracy) <> MAX(accuracy). A WHERE runs before the groups exist and cannot reference either aggregate, so the engine rejects it outright.

Step 3: Sort by model name

ORDER BY mdl_name. After collapsing, each model name appears once, so the alphabetical order is fully determined with no tiebreaker.

SELECT mdl_name, MIN(accuracy) AS min_accuracy, MAX(accuracy) AS max_accuracy
FROM ml_models
GROUP BY mdl_name
HAVING MIN(accuracy) <> MAX(accuracy)
ORDER BY mdl_name

No group filter

GROUP BY mdl_name with MIN and MAX alone returns eight rows. One of them is 'fraud_detect' with both values empty: a model that never moved, reported as if it belongs.

Filter on the range

Adding HAVING MIN(accuracy) <> MAX(accuracy) keeps only groups with a real range, which is seven rows. The same clause would also drop a model whose every version scored identically.

Common follow-up questions

• How would you also return how many versions each kept model has, and does `COUNT(*)` or `COUNT(accuracy)` answer it? (Tests whether the candidate knows `COUNT(accuracy)` skips the `NULL` scores.)
• Rewrite the filter as `COUNT(DISTINCT accuracy) > 1`. Is it equivalent, and which would you ship? (Tests reasoning about two spellings of the same group test and what each costs.)
• Now keep only models whose accuracy moved by more than five points. What changes? (Tests extending the group condition to `MAX(accuracy) - MIN(accuracy) > 0.05`.)