The Apprentices Still in the Forge
An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.
- Domain
- SQL
- Difficulty
- easy
- Seniority
- L3
- Asked in
Problem
How many unique model names in ml_models have at least one deployed version scoring 0.9 accuracy or better? The reviewer auditing the live fleet counts 'Deployed' and 'deployed' as the same status, since that column was never normalized on write.
A model is not a model until it stops learning and starts earning.
Tables
ml_models
Columns: model_id INTEGER, mdl_name TEXT, version TEXT, accuracy REAL, status TEXT, train_at TEXT, framework TEXT
| 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
| high_accuracy_deployed_models |
|---|
| 3 |
Worked solution and explanation
What this really is
This is a filtered unique count with two holes in the filter. A model is not a row: each row of ml_models is one version, so the thing being counted is mdl_name, collapsed. And status was never normalized on write, so the live state is spelled both 'deployed' and 'Deployed'. On this table the casing is not cosmetic: sentiment and price_opt clear 0.9 only on rows stored as 'Deployed'. Match the raw string and the answer drops from 3 to 1. Count rows and it jumps to 14. Either way the reviewer reports a fleet that does not exist.
Step 1: Fold the status casing before filtering
LOWER(status) = 'deployed' turns 'Deployed' and 'deployed' into one value. Most statuses here have a single spelling, but the deployed state does not, and it is the one this question asks about.
Step 2: Apply the accuracy bar to the same rows
accuracy >= 0.9 sits in the same WHERE, so a version qualifies only when it is both live and good enough. Rows with a NULL accuracy fail the comparison and drop out on their own. Every deployed fraud_detect version is one of them, which is correct, since nothing shows it cleared the bar.
Step 3: Count names, not versions
COUNT(DISTINCT mdl_name) collapses the qualifying versions to one per model. churn_pred alone has several qualifying version rows, so a row count answers how many versions qualify, not how many models do.
SELECT COUNT(DISTINCT mdl_name) AS high_accuracy_deployed_models
FROM ml_models
WHERE LOWER(status) = 'deployed'
AND accuracy >= 0.9- Exact match, row count (wrong)
status = 'deployed'withCOUNT(*)keeps only the lowercase rows and counts each version:churn_pred's 6 qualifying rows come back as 6, and sentiment andprice_optvanish.- Folded casing, unique names (right)
LOWER(status)brings sentiment andprice_optback, andCOUNT(DISTINCT mdl_name)turns 14 qualifying version rows into 3 models.
Common follow-up questions
- How would you break this down per `framework`, which also mixes 'PyTorch' and 'pytorch'? (Grouping on a normalized key, not the raw column.)
- What changes if a model should count only when its latest version is the deployed one? (Moves from any-version to latest-version semantics.)
- Should deployed versions with a `NULL` `accuracy` be reported somewhere? (Separating unknown from failing.)