# Average Accuracy by Framework > Not all frameworks deliver the same accuracy. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: ESPN ## Problem The framework accuracy comparison has lost the ML platform team's trust: it shows PyTorch and pytorch as two frameworks and folds in the 2.1 beta and 3.0 releases, because the `version` labels in `ml_models` carry a 'v' prefix or a '-beta' suffix inconsistently. Show each framework's average accuracy across releases 1.0 through 2.0 inclusive, with the framework name in lowercase and the average as a decimal rounded to two places. ## 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 | | --- | --- | | pytorch | 0.8 | | tensorflow | 0.78 | | xgboost | 0.81 | ## Worked solution and explanation ### What this really is This is a data-cleaning problem wearing a dashboard costume. The average is the easy part. The real test is whether you notice that `version` is not a number: 'v1.1', '1.0.0', '3.0' and 'v2.1-beta' all live in the same column. Filter that text as stored and the release window means nothing: you either throw away every prefixed release or let the 2.1 beta and 3.0 models into the averages the team was trying to clean up. The second, quieter trap is the grouping key. The same framework was logged as 'PyTorch' and 'pytorch', 'TensorFlow' and 'tensorflow'. Group on `framework` as stored and the dashboard grows extra rows, each half averaging only part of its models. Nothing errors; the result just looks like more data. #### Step 1: Turn `version` into a release number Strip the 'v' prefix with `REPLACE`, then `CAST` the remainder to a number. Only then does 'v1.1' compare as 1.1. The cast reads the leading release number, so '1.0.0' lands on 1.0 and '2.1-beta' on 2.1, which is all the window needs. Listing the in-window spellings by hand also reaches the right rows today; it just breaks the first time a new label shows up. #### Step 2: Apply the 1.0 through 2.0 window `BETWEEN 1.0 AND 2.0` is inclusive on both ends, which is exactly what 'releases 1.0 through 2.0' asks for. It keeps 'v2.0' and drops 'v2.1-beta' and '3.0', the releases that were inflating the comparison. #### Step 3: Group on one spelling of each framework Group on `LOWER(framework)` and return that same expression, so 'PyTorch' and 'pytorch' collapse into one row, reported in the lowercase spelling the comparison asks for. Profile the column first: the mixed casing is visible in the sample rows. #### Step 4: Average and round `AVG(accuracy)` rounded to two places with `ROUND`. `AVG` skips the models whose `accuracy` is missing, which is the right reading: an unscored model has no accuracy to contribute. **Average accuracy over the 1.0 through 2.0 releases** ```sql SELECT LOWER(framework) AS framework, ROUND(AVG(accuracy), 2) AS avg_accuracy FROM ml_models WHERE CAST(REPLACE(version, 'v', '') AS REAL) BETWEEN 1.0 AND 2.0 GROUP BY LOWER(framework) ``` **Filtering the label as stored** `version BETWEEN '1.0' AND '2.0'` compares text: every 'v'-prefixed release sorts above '2.0' and falls out, so most models vanish from the averages. **Filtering the release number** `CAST(REPLACE(version, 'v', '') AS REAL) BETWEEN 1.0 AND 2.0` compares values: 'v1.1' is 1.1, 'v2.1-beta' is 2.1 and is excluded, exactly as the window intends. > **A dirty grouping key doubles the rows** > > Grouping on raw `framework` returns rows, so it survives review. But 'PyTorch' and 'pytorch' each get their own average, and neither is the framework's real number. > **Profile the column before you filter it** > > The candidates who stand out ask what `version` and `framework` actually hold before writing anything. Saying 'this is text with prefixes, I need a number first' out loud is the signal. > **Version labels are never as clean as the release notes** > > Release labels come from many teams and tools, so prefixes, suffixes and semver depth vary. In production you would normalise once into a numeric column upstream rather than repeating the cleanup in every report. ## Common follow-up questions - A new label like 'v2.0-rc1' shows up. Does it land inside the window, and should it? _(Tests whether the candidate sees that reading the leading number treats a release candidate as the release, and reaches for a pattern-based extraction.)_ - How would you compare '1.10' against '1.9' correctly? _(Tests awareness that casting a dotted version to a single number breaks multi-part semver ordering.)_ - The team now wants only shipped models, and `status` holds both 'deployed' and 'Deployed'. What changes? _(Tests whether the candidate applies the same case-normalisation instinct to a filter column.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/average_accuracy_by_framework) - [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.