Top Accuracy Model

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

Sears

Problem

The registry dashboard crowns a single most accurate and a single least accurate model, yet the ML platform team suspects several models share each of those scores, and entries with no accuracy recorded keep getting mistaken for the worst. From ml_models, list every model at the best or the worst accuracy alongside that accuracy, once per model and score, highest accuracy first and then by model name.

The single best-performing model.

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

This is an extremum-with-ties question in a dashboard costume. The skill is returning every row at the computed maximum and every row at the computed minimum of a column that has holes in it. Anyone can find the best score. What separates candidates is refusing to let a sort pick the winner. On this data two models share 0.99 and two share 0.55, so ORDER BY accuracy DESC LIMIT 1 drops a champion. The ascending version is worse: the 24 rows with a NULL accuracy sort first, and a model nobody ever scored becomes your laggard.

Build it

Step 1: Compute each extreme once

(SELECT MAX(accuracy) FROM ml_models) and (SELECT MIN(accuracy) FROM ml_models) each return one value. Both aggregates skip NULL, so an unscored 'fraud_detect' entry can never be the minimum. You get that guard for free instead of writing it.

Step 2: Keep every row equal to either value

Compare accuracy to both scalars with OR. Equality never matches NULL, so unscored rows drop out a second time. Every tied model at either end survives because the cut is a value, not a row count.

Step 3: Collapse repeat entries

The registry holds the same model at the same score more than once. Without DISTINCT on mdl_name, accuracy the list doubles to 8 lines. Collapse on exactly the two columns you return, nothing wider.

Step 4: Order the report

ORDER BY accuracy DESC, mdl_name puts the champions on top and the laggards below, alphabetical within a score, so two correct answers cannot disagree on sequence.

SELECT DISTINCT
    mdl_name,
    accuracy
FROM ml_models
WHERE accuracy = (SELECT MAX(accuracy) FROM ml_models)
   OR accuracy = (SELECT MIN(accuracy) FROM ml_models)
ORDER BY accuracy DESC, mdl_name

Sort and take one

ORDER BY accuracy DESC LIMIT 1 keeps one of 'churn_pred' and 'sentiment' at 0.99, whichever the engine meets first. Flip it to ASC for the worst and the first row has a NULL accuracy.

Compare to the computed extreme

accuracy = (SELECT MAX(accuracy) ...) returns both models at 0.99, and the MIN side returns both at 0.55, because nothing caps the row count.

Common follow-up questions

• How would you return the best and worst model per `framework` instead of overall? (Moves the extreme from one scalar to one value per group.)
• What if only deployed models should count, given that both 'Deployed' and 'deployed' appear in `status`? (Tests filtering inside the subquery and normalizing case.)
• `accuracy` is a `REAL`. What if two scores differ only in the fifth decimal? (Tests equality on floats and when to round before comparing.)