Category Census

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Problem

Only products rated 4.0 stars or higher make our feature shelf, and a category needs more than four of them to earn a slot. From our products catalog, list each category that qualifies with how many top-rated products it carries, best-stocked first.

Which aisles are worth restocking?

Tables

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

Expected output

Worked solution and explanation

What this really tests

This is a row filter and a group filter wearing a merchandising costume, and the whole question is which phase each one fires in. The rating cutoff thins rows before anything is counted (only products at 4.0 or above qualify); the count cutoff drops whole categories after the tally lands (four or fewer keepers). Anyone can write GROUP BY category with COUNT(*). What separates candidates is putting rating >= 4.0 before the grouping and COUNT(*) > 4 after it. Put the count in WHERE and the engine refuses; skip the row filter and you count almost every product on file, twenty in most aisles, so ten categories sail past the bar and the shelf means nothing.

Two filters, two phases

Step 1: Filter the rows first

WHERE rating >= 4.0 keeps only top-rated rows, before any grouping, so every count you build next is already scoped to four-star stock. It also drops the products with a NULL rating, because a comparison against NULL is never true. This clause creates the real spread: without it, every aisle except Sports and the tiny Collectibles reports a flat 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: the four categories stuck at four and Collectibles with one. It has to be HAVING, because it tests the aggregate the previous step just produced. ORDER BY product_count DESC puts Clothing and its six first. The five categories tied at five can come in any order, since the ask only says best-stocked first.

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

No row filter (wrong)

Skip the WHERE and most categories hold twenty products (Sports eighteen, Collectibles two). HAVING COUNT(*) > 4 keeps ten of the eleven, so the query answers a question nobody asked.

Row filter first (correct)

Filter to rating >= 4.0 first and the counts finally differ: one six, five fives, four fours and a single one. Now HAVING COUNT(*) > 4 has something real to trim, and six categories survive.

Common follow-up questions

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