Competing Standards

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L5

Asked in

Google

Problem

For each model (mdl_name), find the framework used across the greatest number of its versions, breaking a tie in that count by the higher average accuracy. Sort the resulting model-framework pairs by average accuracy, highest first, and keep every pair whose accuracy places it among the three highest values, including any that share a position.

Every framework has a star model.

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

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 over how many of a model's versions use each framework (with accuracy as the 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(DISTINCT version) as the version count and AVG(accuracy) because the next step needs both. Counting distinct versions, not raw rows, is what the phrase "across the most of its versions" means.

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, adding framework as a final tiebreak so the ordering is a total order and the pick is deterministic. 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 any that share a position" on ties is why the filter is on the rank value, not a LIMIT 3.

The solution

WITH fw_counts AS (
  SELECT mdl_name, framework,
         COUNT(DISTINCT version) 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, framework
         ) 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
ORDER BY avg_acc DESC

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 such as framework name.)
• 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.)