# Unmatched Categories

> Categories with nothing on the shelf. Empty aisles.

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

Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: DoubleVerify

## Problem

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

## Tables

### products

- product_id INTEGER
- product_name TEXT
- category TEXT
- price REAL
- rating REAL
- in_stock INTEGER

Sample rows (10 of 200):

| product_id | product_name | category | price | rating | in_stock |
| --- | --- | --- | --- | --- | --- |
| 1001 | Basic Device 1X | Books | 17.52 | 2.7 | 1 |
| 1050 | Deluxe Bundle 2X | Clothing | 25.05 | 4.4 | 1 |
| 1099 | Pro Unit 3X | Home & Kitchen | 32.58 | 2.1 | 1 |
| 1148 | Ultra Tool 4X | Collectibles | 40.11 | 3.8 | 1 |
| 1197 | Essential Set 5X | Toys | 47.64 | 1.5 | 1 |
| 1246 | Classic System 6X | Beauty | 0 | 3.2 | 1 |
| 1295 | Smart Gadget 7X | Automotive | 62.7 | 4.9 | 0 |
| 1344 | Eco Kit 8X | Garden | 70.23 | 2.6 | 1 |
| 1393 | Turbo Pack 9X | Music | 77.76 | 4.3 | 0 |
| 1442 | Premium Widget 10X | Electronics | 85.29 | 2 | 1 |

## Expected output

First 1 of 1 rows:

| category |
| --- |
| Music |

## 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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.