# Average Accuracy by Framework

> Not all frameworks deliver the same accuracy.

Canonical URL: <https://datadriven.io/problems/average_accuracy_by_framework>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The ML platform team is comparing framework performance. Show the average accuracy broken down by framework, limited to deployed models whose version (after stripping any prefixes like 'v' or suffixes like '-beta') falls between 1.0 and 3.0 inclusive. Return the framework and its average accuracy.

## Worked solution and explanation

### Why this problem exists in real interviews

The core skill being tested is grouped aggregation over `ml_models`. Candidates must decide how `mdl_name`, `version`, `accuracy` interact before choosing a join strategy or aggregation level.

---

### Break down the requirements

#### Step 1: Group by `version`

`GROUP BY` at the correct grain produces one row per group.

#### Step 2: Compute `AVG(accuracy)`

The AVG function computes the avg per group.

#### Step 3: Order by the metric

Sort by `avg_accuracy` desc for readability.

---

### The solution

**Group-aggregate for average accuracy framework**

```sql
SELECT
    version,
    AVG(accuracy) AS avg_accuracy
FROM ml_models
GROUP BY version
ORDER BY avg_accuracy DESC
```

> **Cost Analysis**
>
> The main table has 2K rows. The GROUP BY reduces the row count early, keeping downstream operations cheap.

> **Interviewers Watch For**
>
> Strong candidates state the correct `GROUP BY` grain before writing any SQL, showing they think about the output shape first.

> **Common Pitfall**
>
> Selecting a non-aggregated column without including it in `GROUP BY` is the most common error. Some engines reject it; others silently return arbitrary values.

---

## Common follow-up questions

- The `accuracy` column in `ml_models` has roughly 3% NULLs. How does your query handle those rows, and would the result change if NULLs were replaced with zeros? _(Tests whether the candidate understands how NULLs propagate through aggregation functions and whether their WHERE/JOIN conditions implicitly filter them out.)_
- Your GROUP BY aggregates `model_id` from `ml_models`. If two groups have the same aggregate value, how is the output ordered, and is that deterministic? _(Tests awareness that ORDER BY on a non-unique value produces non-deterministic row order without a tiebreaker.)_
- The `mdl_name` column in `ml_models` has a zipf distribution, meaning a few values dominate. How does that skew affect your query plan and parallelism? _(Tests understanding of data skew: the optimizer may choose a bad plan when histogram statistics are stale.)_
- If the business definition of `version` changed mid-quarter (e.g., a status value was renamed), how would you handle historical consistency? _(Tests awareness of slowly changing dimensions and backward-compatible query design.)_

## 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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.