# Top Models by Framework

> Every framework has a star model.

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

Domain: SQL · Difficulty: hard · Seniority: L5

## Problem

For each model (mdl_name), determine the framework used most frequently across its versions. If two frameworks are tied in version count, pick the one with the higher average accuracy. Rank the resulting model-framework pairs by average accuracy from highest to lowest, and return only those in the top 3 ranks. If multiple pairs share the same rank, include all of them.

## Worked solution and explanation

### Why this problem exists in real interviews

Two ranks live inside one prompt and they answer different questions. The per-model choice of framework is a vote (with accuracy as a tiebreaker), while the cross-model leaderboard is a quality ranking. Conflating them into one window is how this gets botched.

---

### Break down the requirements

#### Step 1: Per-(model, framework) rollup

Group `ml_models` by `mdl_name` and `framework`. Carry both `COUNT(*)` as version count and `AVG(accuracy)` because the next step needs both.

#### Step 2: Pick the primary framework per model

Inside each `mdl_name`, order by `ver_count DESC, avg_acc DESC` and keep `ROW_NUMBER() = 1`. ROW_NUMBER, not RANK, because each model returns exactly one framework.

#### Step 3: Cross-model leaderboard with ties

Apply `DENSE_RANK() OVER (ORDER BY avg_acc DESC)` to the survivors. DENSE_RANK keeps ranks 1, 2, 3 contiguous when ties widen the result set.

#### Step 4: Top 3 ranks, not top 3 rows

Filter `rnk <= 3`. The phrase "include all of them" on ties is why the filter is on the rank value, not a `LIMIT 3`.

---

### The solution

**TOP MODELS BY FRAMEWORK**

```sql
WITH fw_counts AS (
  SELECT mdl_name, framework,
         COUNT(*) AS ver_count,
         AVG(accuracy) AS avg_acc
  FROM ml_models
  GROUP BY mdl_name, framework
),
primary_fw AS (
  SELECT mdl_name, framework, avg_acc,
         ROW_NUMBER() OVER (
           PARTITION BY mdl_name
           ORDER BY ver_count DESC, avg_acc DESC
         ) AS rn
  FROM fw_counts
),
best AS (
  SELECT mdl_name, framework, avg_acc
  FROM primary_fw
  WHERE rn = 1
),
ranked AS (
  SELECT mdl_name, framework, avg_acc,
         DENSE_RANK() OVER (ORDER BY avg_acc DESC) AS rnk
  FROM best
)
SELECT mdl_name, framework, avg_acc
FROM ranked
WHERE rnk <= 3
```

> **Cost Analysis**
>
> At 4,000 rows the plan is a single hash aggregate plus two window sorts. Both windows partition or order on small cardinality columns, so memory pressure is negligible. The blocking step is the first GROUP BY scan.

> **Interviewers Watch For**
>
> Whether you separate the two ranks into distinct CTEs, and whether you reach for DENSE_RANK on the outer step. Picking RANK would skip rank values after ties and break the "top 3 ranks" reading of the prompt.

> **Common Pitfall**
>
> Trying to combine framework selection and leaderboard ordering into one window with a clever multi-key ORDER BY. The per-model decision needs to resolve before any cross-model comparison can be valid, so the staging through CTEs is load-bearing.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- If two frameworks tie on version count AND average accuracy within a model, how would you break the deadlock deterministically? _(Probes awareness that ROW_NUMBER picks arbitrarily when the ORDER BY is not a total order, and the fix is an explicit tiebreaker column.)_
- How does the answer change if `accuracy` can be NULL for in-progress training runs? _(Tests knowledge that AVG ignores NULLs but a framework with only NULL accuracies returns NULL, which sorts last under DESC in most dialects.)_
- Rewrite the cross-model leaderboard so ties resolve in favor of the framework with more total versions. _(Forces a second tiebreaker into the outer window and reveals whether the layered CTE design supports that change cleanly.)_

> **Two ranks, two semantics**
>
> ROW_NUMBER answers "which single row wins". DENSE_RANK answers "which tier does this row sit in". Mixing them up is the single most common reason this pattern fails review.

## Related

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