Top Framework by Deployments

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

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Netflix

Problem

Which three models in our ml_models registry have the most entries logged with a status of exactly 'deployed', and what framework does each run on? Only that lowercase spelling marks a live deployment, so 'Deployed' rows count nowhere, not even in the total. Give each model's deployed count and its percentage of all deployed entries, with alphabetically earlier model names (then frameworks) winning any tie at the cutoff.

The framework most often deployed.

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

What this really is

This is a capped leaderboard glued to a fleet-wide denominator, dressed up as a deployment report. Anyone can count deployed rows per model and keep three. The two places candidates lose it: the cutoff, where a tie at third place makes LIMIT 3 pick a winner at random unless the sort says who wins, and the denominator, which has to be every deployed entry in ml_models, not the three leaders added together. Get the second one wrong and the shares sum to 100 and every number is inflated.

Step 1: Filter to exact 'deployed' first

WHERE status = 'deployed' runs before any counting. The registry also holds 'Deployed' and 'DEPLOYED'; those are not live deployments here, so an exact match is the rule, not a case-folded one.

Step 2: Count at the (mdl_name, framework) grain

Every deployed row is one entry, repeat versions included, so this is COUNT(*), not a unique count. Grouping by both columns is what lets the output carry the framework without guessing which one a model runs on.

Step 3: Make the cutoff deterministic

Inside top_models, sort by deploy_count DESC, then mdl_name ASC, then framework ASC, and LIMIT 3. The trailing keys only matter when counts tie at the boundary, and that is exactly when you need them.

Step 4: Divide by the whole fleet

The scalar subquery (SELECT COUNT(*) FROM ml_models WHERE status = 'deployed') counts every deployed entry once, independent of the three rows it sits beside. Multiplying by 100.0 both scales to a percentage and dodges integer division.

WITH deployed_counts AS (
  SELECT mdl_name, framework, COUNT(*) AS deploy_count
  FROM ml_models
  WHERE status = 'deployed'
  GROUP BY mdl_name, framework
),
top_models AS (
  SELECT mdl_name, framework, deploy_count
  FROM deployed_counts
  ORDER BY deploy_count DESC, mdl_name ASC, framework ASC
  LIMIT 3
)
SELECT
  mdl_name,
  framework,
  deploy_count,
  100.0 * deploy_count / (SELECT COUNT(*) FROM ml_models WHERE status = 'deployed') AS deploy_share_pct
FROM top_models

Denominator from the leaders

SUM(deploy_count) OVER () or a sum over top_models divides by the three leaders' entries. The shares always add to 100, which looks tidy and answers nothing.

Denominator from the fleet

A scalar subquery over ml_models filtered to 'deployed' counts every live entry. The three shares add to well under 100, which is the actual concentration story.

Common follow-up questions

• How would you return the top model within each `framework` instead of overall? (Moves the cutoff from global to per-group, where `LIMIT` alone no longer works.)
• If ties at third place should all be kept rather than broken, how does the query change? (Tests whether the candidate can express a cutoff by count value instead of by row position.)
• What changes if 'Deployed' and 'deployed' are later declared the same status? (Checks that both the leaderboard filter and the denominator get normalized together.)