Then and Now

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

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.

Accuracy used to be higher.

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 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 traps

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

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
;

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.)