# Popular Categories > Merchandising only cares about categories big enough to negotiate shelf space. Canonical URL: 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. Lead with the best-reviewed categories 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 | avg_rating | avg_price | | --- | --- | --- | --- | | Automotive | 10 | 4.35 | 909.025 | | Clothing | 10 | 3.95 | 857.7549999999999 | | Garden | 9 | 3.822222222222222 | 823.1522222222222 | | Beauty | 10 | 3.8200000000000003 | 793.091 | | Electronics | 9 | 3.755555555555555 | 922.1588888888889 | ## 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 **Two-stage filter for shelf-worthy categories** ```sql 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 ORDER BY avg_rating DESC ``` > **Common Pitfall** > > The classic error is filtering on the wrong side of the GROUP BY. A rating of 3.0 is a property of a single product, so it belongs in `WHERE`. A count above eight is a property of a whole category, so it can only be tested in `HAVING`. Swap them and the query either refuses to run or scopes the wrong thing. Drop the `WHERE` and every category returns, because all ten already carry twenty products. > **Interviewers Watch For** > > Interviewers watch whether you reach for HAVING the instant a threshold references an aggregate. A candidate who tries to cram `COUNT(*) > 8` into a WHERE clause signals they have not internalized that WHERE runs before rows are grouped, so no group-level count exists there yet. > **Cost Analysis** > > Against 6,000 rows this scans once, groups in memory and sorts a handful of rows; nothing to tune. At real scale you would keep the WHERE predicate sargable so an index on `rating` can pre-filter before the group and sort, shrinking the rows that ever reach the aggregation. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/popular_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.