# The Apprentices Still in the Forge > A model is not a model until it stops learning and starts earning. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: Google ## 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. ## Tables ### ml_models - model_id INTEGER - mdl_name TEXT - version TEXT - accuracy REAL - status TEXT - train_at TEXT - framework TEXT Sample rows (10 of 200): | 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 First 1 of 1 rows: | 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. **Filtered unique count** ```sql 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'` with `COUNT(*)` keeps only the lowercase rows and counts each version: `churn_pred`'s 6 qualifying rows come back as 6, and sentiment and `price_opt` vanish. **Folded casing, unique names (right)** `LOWER(status)` brings sentiment and `price_opt` back, and `COUNT(DISTINCT mdl_name)` turns 14 qualifying version rows into 3 models. > **The exact match runs clean and lies** > > `status = 'deployed'` returns 1 without an error. Nothing reports the rows it skipped, and on this table those rows hold two of the three models that qualify. > **Name both traps before typing** > > The strong answer says out loud that rows are versions and that `status` casing is dirty, then writes four lines. Fixing one and missing the other still produces a confident wrong number. > **One scan, one aggregate** > > A single pass over 200 rows with a scalar `COUNT(DISTINCT ...)`. At catalog scale the real cost is `LOWER` on every row, which also keeps a plain index on `status` from helping. Normalizing on write fixes both. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_apprentices_still_in_the_forge) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.