The Proving Ground

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

ESPN

Problem

Ahead of picking one framework to standardize on for 2026, an analyst is comparing the models in ml_models trained that year, counting the ones with no training timestamp too, since those came in through this year's registry migration. Show each framework's average accuracy, highest first.

One team, one framework. Let the accuracy numbers decide.

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 tests

Strip off the ML costume and this is a per-framework average over a one-year slice, with two traps in it. First, framework is entered by hand: 'PyTorch' and 'pytorch' are one framework, and so are 'TensorFlow' and 'tensorflow'. Second, undated models count toward the year, and a NULL train_at fails every comparison you can write against it. On this data every undated model carries the 'PyTorch' label. A year filter alone quietly removes all of them, and the pytorch average comes out wrong while everything else looks fine.

Building it up

Step 1: Define the slice

Keep a model when the year of train_at is 2026 or when train_at is missing. The two halves of the OR cover different rows: the year test admits the dated models, and train_at IS NULL admits the migration rows the year test can never see.

Step 2: Fold the framework label

Wrap the label in LOWER(framework). The undated 'PyTorch' rows now land in the same bucket as the dated 'pytorch' rows, and the two TensorFlow spellings merge, so each framework comes out as one row.

Step 3: Group and average

GROUP BY LOWER(framework) with AVG(accuracy) AS avg_accuracy. AVG skips NULL accuracy, so an unscored model simply does not count, rather than being averaged in as zero.

Step 4: Order highest first

ORDER BY avg_accuracy DESC puts the strongest framework on top, which is the order the statement asks for.

The solution

SELECT LOWER(framework) AS framework,
       AVG(accuracy) AS avg_accuracy
FROM ml_models
WHERE strftime('%Y', train_at) = '2026'
   OR train_at IS NULL
GROUP BY LOWER(framework)
ORDER BY avg_accuracy DESC

Common follow-up questions

• How would you also show frameworks that had no models in the slice? (Needs a framework dimension with a left join, or a zero-fill.)
• What other label inconsistencies could still split a framework? (Padding handled with `TRIM`, and aliases like sklearn vs scikit-learn.)
• How would you report how many scored models stand behind each average? (Tests counting non-null `accuracy` instead of rows.)
• How would you convince yourself this is right on a fresh, larger dataset? (Distinct-value checks, per-group counts, reconciling against a known total.)