# Top Frameworks by Accuracy > Top three frameworks by accuracy. Canonical URL: 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. ## 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 3 of 3 rows: | framework | avg_accuracy | accuracy_rank | lift_over_fleet | | --- | --- | --- | --- | | pytorch | 0.8195999999999999 | 1 | 0.029031818181818103 | | xgboost | 0.8138461538461539 | 2 | 0.023277972027972105 | | tensorflow | 0.7806060606060606 | 3 | -0.009962121212121144 | ## 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 **Framework leaderboard, best three with position and fleet lift** ```sql 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 ``` > **Why spell out the average** > > SUM(accuracy) / COUNT(*) is written out on purpose so the null-handling decision is auditable rather than hidden inside AVG. The tradeoff: it is only correct with the null filter in place; AVG(accuracy) is the equally-correct one-line shortcut that skips nulls for you. Notice the fleet lift for the third framework is slightly negative: tensorflow makes the top three yet sits just under the fleet-wide average, because pytorch and xgboost pull that house average up. That is honest, not a bug. **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 pitfall** > > The denominator bug and the casing split. Writing SUM(accuracy) / COUNT(*) without excluding unscored models leaves null-accuracy rows in COUNT(*), so a framework with many unevaluated models looks worse than it is; and grouping on the raw framework column scores one tool as several. Filter the nulls (or use AVG), lower the name before grouping, and remember the baseline subquery needs the same null filter. > **Interviewers watch for** > > Three tells separate seniors here: they LOWER the framework name before grouping, they say out loud what happens to unscored models before writing the mean, and they carry the same null filter into the fleet-wide baseline subquery. Candidates who skip any one produce a plausible but wrong leaderboard. > **Cost analysis** > > GROUP BY LOWER(framework) collapses `ml_models` to a handful of framework groups in a single scan, and the scalar baseline is one more scan the planner can cache. A functional index on LOWER(framework) lets the engine group without a full sort, and ordering a handful of groups before LIMIT 3 is effectively free. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/top_frameworks_by_accuracy) - [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.