Popular Categories

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

The merchandising team is choosing which categories to pitch for premium shelf space and only cares about ones with real depth of well-reviewed inventory, counting only products rated 3.0 or higher. Find the categories that carry more than eight such products, and for each show how many there are along with their average rating and average price.

Merchandising only cares about categories big enough to negotiate shelf space.

Tables

products

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

Expected output

Worked solution and explanation

The two filters hiding in one question

This is a two-stage filter wearing a merchandising costume: a WHERE that narrows the raw rows down to well-reviewed products, then a HAVING that keeps only the categories deep enough in them. Both are load-bearing, and here is why. Every category in this catalog carries the same twenty products, so a plain count ties them all and separates nothing. The discrimination only shows up once you count the products that clear the rating bar, and that per-category count does not exist until the rows are grouped, which is exactly what WHERE cannot reach. The trap is folding the two tests into one stage: put the count in the WHERE and it is illegal, put the rating in the HAVING and you scope categories on the wrong thing. Either way you hand back every category or the wrong ones.

Break it down

Step 1: Filter the raw products first

WHERE rating >= 3.0 runs before any grouping, so only well-reviewed products survive to be counted. Rows with a NULL rating drop out here, which is what you want: no rating is not a rating of three.

Step 2: Collapse to one row per category

GROUP BY category collapses the surviving rows into one group per category.

Step 3: Compute the three metrics

COUNT(*), AVG(rating) and AVG(price) describe the well-reviewed slice of each category.

Step 4: Keep only the deep categories

HAVING COUNT(*) > 8 runs after grouping and drops any category holding eight or fewer qualifying products. This test cannot live in WHERE: the per-category count does not exist until the rows are grouped.

Step 5: Order by the computed rating

ORDER BY avg_rating DESC leads with the best-reviewed categories, the way the team opens its pitch. The seven qualifying categories all land on distinct averages, so the order is deterministic without a tiebreaker.

The solution

SELECT
    category,
    COUNT(*) AS product_count,
    AVG(rating) AS avg_rating,
    AVG(price) AS avg_price
FROM products
WHERE rating >= 3.0
GROUP BY category
HAVING COUNT(*) > 8

Common follow-up questions

• How would the categories change if you moved the rating test out of WHERE and expressed it as `HAVING AVG(rating) >= 3.0` instead? (Tests understanding of per-row (WHERE) versus per-group (HAVING) filtering and how the result set changes.)
• If two categories tied on average rating, how would you make the output order deterministic? (Tests awareness that ORDER BY must be total to be deterministic under ties.)
• The cutoff of more than eight is a fixed literal. How would you restructure the query to keep, say, the top quartile of categories by depth instead? (Tests replacing a static HAVING literal with a dynamic, data-driven threshold.)