# Unmatched Categories

> Categories with nothing on the shelf. Empty aisles.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

Which product categories have zero products currently in stock? These represent gaps where customers can browse but cannot buy.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests conditional aggregation with `HAVING`. Interviewers check whether you can express a group-level zero condition cleanly, which is a common inventory analysis pattern.

---

### Break down the requirements

#### Step 1: Group by category

`GROUP BY category` partitions the 12K products into 20 categories.

#### Step 2: Filter to categories with zero in-stock products

`HAVING SUM(in_stock) = 0` finds categories where no product has `in_stock = 1`. Since `in_stock` is 0 or 1, summing gives the count of in-stock items.

---

### The solution

**HAVING filter for zero-stock categories**

```sql
SELECT category
FROM products
GROUP BY category
HAVING SUM(in_stock) = 0
```

> **Cost Analysis**
>
> Full scan of 12K rows, grouped into 20 categories. Trivially fast. No index needed for a table this size.

> **Interviewers Watch For**
>
> Alternative approaches: `HAVING MAX(in_stock) = 0` or `NOT EXISTS` with a subquery. All are valid; the interviewer wants to see you choose one and justify it.

> **Common Pitfall**
>
> Using `WHERE in_stock = 0` and then SELECT DISTINCT category. This finds categories that have some out-of-stock products, not categories where ALL products are out of stock.

---

## Common follow-up questions

- What if in_stock could be NULL for some products? _(SUM ignores NULLs, so NULLs would not count as in-stock. Decide if NULL means 'unknown stock' or 'out of stock'.)_
- How would you also show the count of products per empty category? _(Add COUNT(*) to the SELECT; it is already grouped correctly.)_
- How would you find categories where less than 10% of products are in stock? _(Change HAVING to `SUM(in_stock) * 1.0 / COUNT(*) < 0.1`.)_

## Related

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