# Still Running on Fumes

> Production never forgets the models you forgot to retire.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The ML platform team is deprecating old models to free up serving capacity. Before they archive anything, they need a count of how many unique models currently carry a deprecated status.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests COUNT(DISTINCT) with a WHERE filter. It screens for the ability to count unique values within a filtered subset.

---

### Break down the requirements

#### Step 1: Filter and count

`WHERE status = 'deprecated'` restricts to deprecated models. `COUNT(DISTINCT mdl_name)` counts unique model names.

---

### The solution

**Filtered distinct count**

```sql
SELECT COUNT(DISTINCT mdl_name) AS deprecated_models
FROM ml_models
WHERE status = 'deprecated'
```

> **Cost Analysis**
>
> Scan of 1,000 rows. Trivially fast.

> **Interviewers Watch For**
>
> Whether the candidate uses COUNT(DISTINCT mdl_name) vs COUNT(*). The prompt says "unique models," and a model may have multiple versions, so DISTINCT on the name is correct.

> **Common Pitfall**
>
> Using COUNT(*) would count deprecated rows, not unique deprecated models. If a model has 3 deprecated versions, COUNT(*) returns 3 while COUNT(DISTINCT mdl_name) returns 1.

---

## Common follow-up questions

- What is the difference between COUNT(*) and COUNT(DISTINCT mdl_name)? _(COUNT(*) counts all matching rows; DISTINCT counts unique model names.)_
- How would you also show the count per framework? _(Add GROUP BY framework.)_
- What if you needed models deprecated in the last 30 days? _(Add a date filter on train_at or a deprecation timestamp.)_

## Related

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