Top Frameworks by Accuracy

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

L4

Asked in

Forbes

Problem

Our model registry ml_models records every model we've trained, its framework, and its accuracy, but models that were never evaluated have no accuracy on file. We want a leaderboard of the three frameworks with the highest average accuracy across their scored models, each shown with its position on the board and how its average stacks up against the fleet-wide average over all scored models. Treat framework names that differ only in capitalization as one tool, and order the board best first.

Top three frameworks by accuracy.

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 is really testing

Build it in four moves

Step 1: Collapse the casing first

The framework column holds hand-typed names, so 'TensorFlow' and 'tensorflow' are the same tool spelled two ways. GROUP BY LOWER(framework) folds every casing variant into one group. Grouping on the raw column scatters one framework's models across several rows and understates its true model count.

Step 2: Average only the scored models

Some models carry a null accuracy: they were never evaluated. Because the mean is spelled out as SUM(accuracy) / COUNT(*), a null row would remain in COUNT(*) and inflate the denominator, pulling the average down. WHERE accuracy IS NOT NULL drops those rows so the denominator counts only scored models. The same filter has to appear inside the fleet-wide baseline subquery, or the comparison is measured against a corrupted denominator.

Step 3: Stamp each framework with its position

The board needs a position number per framework, so DENSE_RANK() OVER (ORDER BY SUM(accuracy) / COUNT(*) DESC) stamps each framework with its standing. The window runs over every framework group before the top slice is taken, so the surviving three come back numbered 1, 2, 3.

Step 4: Rank against the fleet, then take the best three

A scalar subquery, SELECT SUM(accuracy) / COUNT(*) FROM ml_models WHERE accuracy IS NOT NULL, gives the fleet-wide average across all scored models; subtracting it from each framework's average shows how far above or below the house each leader sits. ORDER BY avg_accuracy DESC with framework as a deterministic tiebreak puts the strongest first, and LIMIT 3 keeps the top three.

The solution

SELECT LOWER(framework) AS framework,
       SUM(accuracy) / COUNT(*) AS avg_accuracy,
       DENSE_RANK() OVER (ORDER BY SUM(accuracy) / COUNT(*) DESC) AS accuracy_rank,
       SUM(accuracy) / COUNT(*) - (SELECT SUM(accuracy) / COUNT(*) FROM ml_models WHERE accuracy IS NOT NULL) AS lift_over_fleet
FROM ml_models
WHERE accuracy IS NOT NULL
GROUP BY LOWER(framework)
ORDER BY avg_accuracy DESC, framework
LIMIT 3

SUM(accuracy) / COUNT(*), no null filter

COUNT(*) counts unscored models too, so pytorch and xgboost crater to roughly 0.62 and the board reorders to tensorflow, sklearn, xgboost: the wrong top three.

SUM(accuracy) / COUNT(*) WHERE accuracy IS NOT NULL

The denominator counts only scored models, matching AVG(accuracy). pytorch (0.82) and xgboost (0.81) hold the top two and the board is honest.

Common follow-up questions

• How would you also report how many scored models back each framework's average, so a framework with two models isn't ranked beside one with two hundred? (Tests adding COUNT(*) to the grouped query and reasoning about sample-size reliability.)
• If leadership only trusts frameworks with at least 20 scored models, where does that threshold go? (Tests HAVING versus WHERE placement on a post-aggregation count.)
• If two frameworks genuinely tie for third by average, how would you decide what `DENSE_RANK` and LIMIT return, and make it stable across runs? (Tests deterministic tiebreak design and total ordering under a window rank.)