# Then and Now > Accuracy used to be higher. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L5 · Asked in: Google ## Problem For the ML platform team's drift review, what does each model in `ml_models` look like: its average accuracy across its counted versions, the accuracy of its most recent version, and the gap between that latest score and the average of its earlier versions? A version counts only when both its accuracy and a non-blank training time are recorded, and when two versions share a training time the higher `model_id` is the newer one. A model with a single counted version has no earlier versions, so its gap is 0. ## 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 5 of 7 rows: | model_name | avg_lifetime_accuracy | latest_accuracy | difference | | --- | --- | --- | --- | | churn_pred | 0.9 | 0.56 | -0.36 | | demand_fcst | 0.73 | 0.74 | 0.01 | | img_classify | 0.84 | 0.83 | -0.01 | | price_opt | 0.9 | 0.92 | 0.03 | | rec_engine | 0.78 | 0.83 | 0.05 | ## Worked solution and explanation ### What this really is This is a latest-versus-baseline comparison wearing an ML-monitoring costume. The real skill is computing two DIFFERENT averages per model from a single scan: the lifetime average you display, and the average of every version EXCEPT the latest, which is the baseline the gap actually measures against. Anyone can write the window function. What separates candidates is noticing that the baseline for the gap is not the lifetime average, and that a row with no accuracy or no training time is not a version at all. Miss either point and the numbers drift by a hundredth in every row. > **The two averages are not the same number** > > `avg_lifetime_accuracy` averages ALL counted versions, including the newest. The gap, though, is latest minus the average of the EARLIER versions only. If you subtract the lifetime average from the latest, the latest row sits on both sides of the subtraction and the drift comes out too small. Compute both averages side by side in one pass: AVG(accuracy) for lifetime, AVG(CASE WHEN rn > 1 THEN accuracy END) for the baseline. --- ### The traps > **Dirty rows are not versions** > > A row with NULL accuracy or a missing `train_at` cannot be scored or placed in time, so it has to be removed BEFORE you count versions, not silently skipped by AVG. Leave a NULL-accuracy row in and it still inflates COUNT(*) OVER, so a model with one real version looks like it has two and your single-version guard never fires. Filter accuracy IS NOT NULL AND `train_at` <> '' in the first CTE, then count. A blank `train_at` fails the <> '' test, and a NULL `train_at` fails it too (NULL <> '' is not true), so one predicate catches both. > **Latest means latest by time, with a tiebreaker** > > The most recent version is ordered by `train_at` descending, but if two versions share a timestamp `ROW_NUMBER` picks one arbitrarily and your `latest_accuracy` flickers between runs. Add `model_id` DESC as a deterministic tiebreaker so rn = 1 is always the same row. Also confirm `train_at` compares chronologically: it is stored as TEXT here, and the ISO layout sorts correctly as text, but a different format would need a cast. #### Step 1: Keep only real versions and number them In the valid CTE, drop rows without an accuracy or a training time. Then `ROW_NUMBER`() OVER (PARTITION BY `mdl_name` ORDER BY `train_at` DESC, `model_id` DESC) tags the newest surviving version as rn = 1, and COUNT(*) OVER (PARTITION BY `mdl_name`) gives the version count for the single-version guard, both without collapsing the rows yet. #### Step 2: Compute the display average and the baseline in one pass In the agg CTE, group by model and pull three numbers: `latest_accuracy` via MAX(CASE WHEN rn = 1 THEN accuracy END), `avg_lifetime_accuracy` via AVG(accuracy) over all counted versions, and `avg_previous_accuracy` via AVG(CASE WHEN rn > 1 THEN accuracy END). The first is the newest row, the last is the baseline the gap subtracts. #### Step 3: Assemble the row and guard single-version models Round the lifetime average and latest accuracy to two decimals. For the gap, use CASE WHEN `n_versions` = 1 THEN 0 ELSE ROUND(`latest_accuracy` - `avg_previous_accuracy`, 2), because a lone version has no earlier versions and its `avg_previous_accuracy` is NULL, which would otherwise turn the whole subtraction into NULL. Order by `model_name` for a stable, alphabetical result. --- ### The solution **Two averages from one scan, latest excluded from the baseline** ```sql WITH valid AS ( SELECT mdl_name, accuracy, train_at, ROW_NUMBER() OVER (PARTITION BY mdl_name ORDER BY train_at DESC, model_id DESC) AS rn, COUNT(*) OVER (PARTITION BY mdl_name) AS n_versions FROM ml_models WHERE accuracy IS NOT NULL AND train_at <> '' ), agg AS ( SELECT mdl_name, n_versions, MAX(CASE WHEN rn = 1 THEN accuracy END) AS latest_accuracy, AVG(accuracy) AS avg_lifetime_accuracy, AVG(CASE WHEN rn > 1 THEN accuracy END) AS avg_previous_accuracy FROM valid GROUP BY mdl_name, n_versions ) SELECT mdl_name AS model_name, ROUND(avg_lifetime_accuracy, 2) AS avg_lifetime_accuracy, ROUND(latest_accuracy, 2) AS latest_accuracy, CASE WHEN n_versions = 1 THEN 0 ELSE ROUND(latest_accuracy - avg_previous_accuracy, 2) END AS difference FROM agg ; ``` > **Interviewers watch for** > > The tell is whether you pick the right baseline. A candidate who computes one average and subtracts it from the latest has misread the question; a senior one names the two averages out loud and explains why the latest row must be excluded from the baseline. The second tell is filtering the dirty rows before counting versions, not after, so the single-version guard stays honest. Notice too that a blank `train_at` needs no separate null check: the <> '' test already fails for both blanks and nulls. > **Cost at scale** > > Two window functions plus one grouped pass over 5K rows is cheap, and both windows share the same PARTITION BY `mdl_name`, so the engine sorts once and reuses the ordering for the row number and the count. At hundreds of millions of rows, an index on (`mdl_name`, `train_at` DESC) lets the newest-version lookup and the partition boundary come straight off the index instead of a full sort. --- ## Common follow-up questions - The product team now wants the gap measured against only the two most recent prior versions instead of all earlier ones. How does your query change? _(Tests whether the candidate keeps the lifetime average and the gap baseline distinct rather than collapsing them into one number.)_ - If two versions of the same model share a `train_at`, which one becomes the latest, and how would you make that choice reproducible? _(Tests understanding of tie behavior when the training timestamp is not unique per model.)_ - If `ml_models` grows to hundreds of millions of rows across many models, how would you index or partition it to keep the newest-version lookup fast? _(Tests indexing and partitioning strategy for time-ordered version history at scale.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/then_and_now) - [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.