Average Accuracy by Framework

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

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.

Not all frameworks deliver the same 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 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.

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.

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