Deployed Models by Framework

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

Domain: SQL
Difficulty: easy
Seniority: L3
Asked in: Forbes

Problem

Each row in ml_models is one registration of a trained model, and a registration is live only when its status reads exactly 'deployed'. How many live registrations does each model name have?

Which frameworks are actually in production?

Tables

ml_models

Columns: model_id INTEGER, mdl_name TEXT, version TEXT, accuracy REAL, status TEXT, train_at TEXT, framework TEXT

ml_models: 10 of 200 rows
model_id	mdl_name	version	accuracy	status	train_at	framework
123	rec_engine	v1.1	0.72	training	2026-02-02 01:00:00	TensorFlow
146	churn_pred	v2.0	0.89	archived	2026-03-03 02:00:00	XGBoost
169	spam_filter	3.0	0.61	failed	2026-04-04 03:00:00	sklearn
192	img_classify	v2.1-beta	0.78	validating	2026-05-05 04:00:00	pytorch
215	sentiment	1.0.0	0.95	Deployed	2026-06-06 05:00:00	tensorflow
238	demand_fcst	v1.0	0.67	deployed	NULL	PyTorch
261	price_opt	v1.1	0.84	Training	2026-08-08 07:00:00	TensorFlow
284	fraud_detect	v2.0	NULL	archived	2026-09-09 08:00:00	XGBoost
307	rec_engine	3.0	0.73	failed	2026-01-10 09:00:00	sklearn
330	churn_pred	v2.1-beta	0.9	validating	2026-02-11 10:00:00	pytorch

Expected output

First 4 of 4 rows
mdl_name	live_registrations
churn_pred	8
demand_fcst	8
fraud_detect	8
img_classify	8

Worked solution and explanation

What this really is

This is a filter wearing an aggregation costume. Anyone can write GROUP BY mdl_name with COUNT(*); the question is whether you throw away the non-live rows first, and whether you throw away the right ones. Skip the filter and every model is padded with its training, archived and failed registrations. Fold the casing with LOWER(status) and the 'Deployed' rows sneak back in. Either way the query runs, and every number on the page is wrong.

Build it in order

Step 1: Filter to live registrations

Keep only rows where status = 'deployed'. This is a condition on each registration, so it belongs in WHERE, which runs before any grouping happens.

Step 2: Count per `mdl_name`

Group the survivors by mdl_name and count rows with COUNT(*). Each surviving row is one live registration, so the row count is the answer.

Live registrations per model

SELECT mdl_name, COUNT(*) AS live_registrations
FROM ml_models
WHERE status = 'deployed'
GROUP BY mdl_name

Filter in WHERE: WHERE status = 'deployed' removes rows before GROUP BY, so COUNT(*) only ever sees live registrations.
Filter in HAVING: HAVING sees groups, not rows. A row-level test on status there either errors or checks one arbitrary row per group, and the counts still include every status.

Common follow-up questions

How would you also report the average `accuracy` of each model's live registrations, given some `accuracy` values are `NULL`? (Adds a second aggregate over the same filtered rows.)
What changes if models with zero live registrations must still appear with a count of 0? (Moves the status test inside the aggregate so all models appear.)
If the business decides 'Deployed' and 'deployed' are the same state, where would you fix that? (Tests whether the candidate separates a data fix from a query workaround.)