# Category Census

> Which aisles are worth restocking?

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The warehouse team is prepping inventory numbers for the quarterly review. They need to see how many products sit in each category, but only categories that carry more than eight items are worth discussing. Show the category and the product tally, sorted from largest category to smallest.

## Worked solution and explanation

### Why this problem exists in real interviews

This is a fundamental GROUP BY with HAVING question. It screens for the ability to aggregate, filter on aggregate results, and sort, which are the building blocks of every analytics query.

---

### Break down the requirements

#### Step 1: Group by category

`GROUP BY category` on the `products` table produces one row per category with `COUNT(*)` as the product tally.

#### Step 2: Filter and sort

`HAVING COUNT(*) > 8` removes small categories. `ORDER BY COUNT(*) DESC` lists largest first.

---

### The solution

**Group, filter, and sort**

```sql
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 8
ORDER BY product_count DESC
```

> **Cost Analysis**
>
> A single scan of 6K rows with in-memory aggregation. Trivially fast. No optimization needed.

> **Common Pitfall**
>
> Using `WHERE COUNT(*) > 8` instead of `HAVING` is a syntax error. Aggregate filters belong in the HAVING clause, which runs after GROUP BY.

---

## Common follow-up questions

- What is the difference between WHERE and HAVING? _(Fundamental SQL concept: WHERE filters rows before grouping, HAVING filters groups after aggregation.)_
- What if you needed categories with exactly 8 products? _(Tests boundary precision: change > 8 to = 8.)_
- How would you also show the average price per category? _(Adding AVG(price) to SELECT tests comfort with multiple aggregates.)_

## 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). 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.