# The Proving Ground

> One team, one framework. Let the accuracy numbers decide.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

The ML team is deciding which framework to standardize on for 2026 and wants an accuracy comparison. Show each framework's average accuracy among models trained that year, from highest to lowest.

## Worked solution and explanation

### What this really tests

Strip off the ML costume and this is a case-insensitive grouped average over a one-year slice. The skill being probed is whether you notice that framework is entered by hand and comes in every casing: 'TensorFlow', 'tensorflow', 'PyTorch', 'pytorch'. Group by the raw column and each framework splits into two rows, so every average is computed over half the data and the leaderboard is quietly wrong. Anyone can write AVG plus GROUP BY. The tell is whether you normalize the label first.

> **Trick to solving**
>
> The one move that cracks this: GROUP BY LOWER(framework), not GROUP BY framework. It folds 'TensorFlow', 'tensorflow' and any other casing into a single bucket before AVG runs, so each framework contributes exactly one row with one honest average.

---

### Building it up

#### Step 1: Scope to the year and the scored models

Keep only models trained in the target year using strftime('%Y', train_at) against the year, and drop rows where accuracy IS NULL. A missing accuracy is not a zero; excluding it keeps the average from being dragged down (AVG ignores NULLs anyway, but stating it makes intent clear). Rows with a NULL train_at also fall out here.

#### Step 2: Normalize the framework label

Wrap the label in LOWER(framework). This is the load-bearing line: the same framework logged as 'PyTorch' and 'pytorch' now collapses to one group, so the business gets one accuracy figure per framework instead of two half-figures.

#### Step 3: Group and average

GROUP BY LOWER(framework) and compute AVG(accuracy) AS avg_accuracy. Each surviving group emits one framework and its mean accuracy across the year's scored models.

#### Step 4: Order highest to lowest

ORDER BY avg_accuracy DESC puts the strongest framework on top. Add framework ASC as a secondary key so equal averages come out in a stable, alphabetical order rather than whatever the engine happens to return.

### The solution

**Case-insensitive average per framework**

```sql
SELECT LOWER(framework) AS framework, AVG(accuracy) AS avg_accuracy
FROM ml_models
WHERE strftime('%Y', train_at) = '2026'
  AND accuracy IS NOT NULL
GROUP BY LOWER(framework)
ORDER BY avg_accuracy DESC, framework ASC
```

> **Common pitfall**
>
> Grouping by the raw framework column is the mistake that fails this quietly. The query still runs and still returns numbers, but 'TensorFlow' and 'tensorflow' become separate rows, each averaged over half the models. Nothing errors; the answer is just wrong. Always normalize the grouping key when a text dimension is entered by hand.

> **Interviewers watch for**
>
> The give-away of a careful candidate is that they inspect the distinct values of framework before grouping and catch the casing drift, and that they treat NULL accuracy as absent rather than zero. Both are cheap to say out loud and both change the result.

> **Why it stays cheap**
>
> This is a single sequential scan of ml_models with a hash aggregate; no joins, no subqueries. LOWER() is a cheap per-row function and does not block the aggregate. Even at a few million rows this is one pass plus a small sort over the handful of distinct frameworks, so it stays inexpensive.

---

## Common follow-up questions

- How should models with a NULL accuracy affect each framework's average? _(Tests whether they treat a missing metric as absent versus zero, and whether they know AVG skips NULLs.)_
- How would you also show frameworks that trained no models that year? _(Tests awareness that an inner filter drops non-participating groups; needs a left join against a framework dimension or a zero-fill.)_
- What other label inconsistencies could still split a framework into two groups? _(Tests robustness of the normalization beyond casing: trimming whitespace, unifying aliases like sklearn vs scikit-learn.)_
- How would you convince yourself this is correct on a fresh, larger dataset? _(Tests verification instinct: distinct-value checks, per-group row counts, reconciling against a known total.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_proving_ground)
- [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.