# Category Census

> Which aisles are worth restocking?

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The merchandising team is building a feature shelf from top-rated stock, meaning products rated 4.0 stars or higher. Show each category that carries more than four of them along with that count, the best-stocked category first.

## 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 5 of 6 rows:

| category | product_count |
| --- | --- |
| Clothing | 6 |
| Automotive | 5 |
| Beauty | 5 |
| Books | 5 |
| Music | 5 |

## Worked solution and explanation

### What this really tests

This is the WHERE-versus-HAVING distinction wearing a merchandising costume. There are two filters here and they fire in two different phases: one thins the rows before you count anything (only four-star-plus products qualify), the other drops whole categories after the count lands (fewer than five keepers). Anyone can GROUP BY and COUNT. The tell is whether you know the rating cutoff has to happen BEFORE the grouping and the count cutoff AFTER it. Put the count in WHERE and the engine throws; filter rating in HAVING and you end up counting every product on file, a flat twenty per aisle, so the threshold trims nothing and the list is meaningless.

---

### Two filters, two phases

#### Step 1: Filter the rows first

`WHERE rating >= 4.0` keeps only the top-rated rows. This runs per-row, before any grouping, so every count you build next is already scoped to four-star stock. This is the clause that creates a real spread across categories; without it, each aisle just reports its full twenty.

#### Step 2: Group and count

`GROUP BY category` collapses the survivors into one row per aisle, and `COUNT(*)` tallies the top-rated products in each. The count does not exist until this step completes, which is exactly why the next filter cannot be a WHERE.

#### Step 3: Filter the groups, then order

`HAVING COUNT(*) > 4` drops the thin aisles. It has to be HAVING, not WHERE, because it tests the aggregate the previous step just produced. `ORDER BY product_count DESC, category ASC` lists the deepest first; the alphabetical tiebreak matters here because several aisles tie at five, and without it their order is undefined.

**Row filter, then group filter**

```sql
SELECT category, COUNT(*) AS product_count
FROM products
WHERE rating >= 4.0
GROUP BY category
HAVING COUNT(*) > 4
ORDER BY product_count DESC, category ASC
```

*WHERE scopes to top-rated rows; HAVING trims the shallow categories; the second sort key makes the order total.*

> **Common pitfall**
>
> Writing `WHERE COUNT(*) > 4` is a straight syntax error: aggregates are not allowed in WHERE, which runs before the grouping exists. The quieter mistake is moving the rating test into HAVING. That counts all twenty products per aisle, every category clears any small threshold, and the feature shelf fills with junk.

**No row filter (wrong)**

Skip the WHERE and every category still holds its full twenty products. HAVING COUNT(*) > 4 keeps all ten aisles, so the query answers a question nobody asked.

**Row filter first (correct)**

Filter to rating >= 4.0 first and the per-category counts finally differ (a six, a cluster of fives, some fours). Now HAVING > 4 has something real to trim, and seven aisles survive.

> **Interviewers watch for**
>
> The tell of a strong candidate: they reach for WHERE the instant the predicate is about a row (rating) and for HAVING the instant it is about the aggregate (the count), without deliberating. A weaker candidate tries to cram both conditions into one clause and then debugs the error.

> **Performance insight**
>
> One sequential scan of the table with in-memory aggregation. On six thousand rows it is instant, and there is nothing to tune; the shape of the query, not its cost, is the whole point.

---

## Common follow-up questions

- Why does the rating filter go in WHERE but the count filter go in HAVING? _(The core concept: WHERE filters rows before grouping, HAVING filters groups after aggregation.)_
- How would you list only categories with exactly five top-rated products? _(Boundary precision: switch the strict > to an equality on the aggregate.)_
- How would you also show each surviving category's average rating? _(Adding AVG(rating) to the SELECT tests comfort with a second aggregate over the same groups.)_

## Related

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