# Leading ML Frameworks by Accuracy

> Which frameworks lead on accuracy?

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

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

### Why this problem exists in real interviews

This probes whether you can combine aggregation with window functions without conflating grain, and date arithmetic and temporal grouping. It is a common at L3/L4+ screen because it requires composing multiple SQL features correctly in a single pass, and small mistakes in logic produce silently wrong results.

> **Trick to Solving**
>
> Mode requires finding the most frequent value. The trick is combining `COUNT` with `ORDER BY DESC LIMIT 1`.
> 
> 1. Group by the target column and count occurrences
> 2. Order by count descending
> 3. Handle ties if the prompt requires it

---

### Break down the requirements

#### Step 1: Filter to the target rows

Filter for NULL or non-NULL values in the `WHERE` clause. This must happen before aggregation to avoid corrupted results.

#### Step 2: Aggregate with AVG

Group by the output grain and apply `AVG()` to compute the metric. The `GROUP BY` must match exactly what the output needs: one row per group key.

#### Step 3: Order the final output

Apply `ORDER BY` as specified to produce the expected row sequence. When tied values exist, add a secondary sort column for determinism.

---

### The solution

**Filtered aggregate ranked by average**

```sql
SELECT framework, AVG(accuracy) AS avg_accuracy
FROM ml_models
WHERE accuracy IS NOT NULL
GROUP BY framework
ORDER BY avg_accuracy DESC
```

> **Cost Analysis**
>
> The query scans 2K rows from `ml_models`. The window function requires a sort, which is O(n log n). Pre-aggregating reduces the sort input.

> **Interviewers Watch For**
>
> Strong candidates explain their choice of window function (`ROW_NUMBER` vs `RANK` vs `DENSE_RANK`) and why it matches the tie semantics. Familiarity with statistical functions beyond COUNT/SUM/AVG distinguishes senior candidates.

> **Common Pitfall**
>
> Applying a window function before aggregating inverts the grain. Always aggregate first, then rank or compare across groups.

---

## Common follow-up questions

- Could you solve this without a window function? _(A self-join or correlated subquery is valid but typically slower.)_
- How would the query change if data volume increased 10x? _(Tests consideration of index strategy, partitioning, or materialized views.)_
- What if you needed to handle timezone differences across regions? _(Tests awareness of timestamp normalization in production.)_
- What if you needed to exclude outliers before aggregating? _(Tests knowledge of percentile cutoffs or z-score filtering.)_
- How would you validate that this query is correct on a new dataset? _(Tests approach to verification: spot checks, known aggregates, or row-count sanity.)_

## Related

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