Clean Averages

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

Across the products table, which categories have at least three rated products, and for each one, how many rated products does it have and what is their average rating to one decimal place? A product that was never rated has no score and counts toward neither figure.

Merchandising only cares about the categories customers actually rate.

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 is really asking

This is a population-alignment problem dressed up as a quality report. Two numbers come back per category, and they have to describe the same set of products. Anyone can write AVG(rating). The trick is that AVG quietly skips null ratings while COUNT(*) counts every row, so a query with no filter returns a correct average sitting next to a wrong count. Get it wrong and every category with an unscored product overstates how many ratings stand behind its average, and a thin category can sneak past the three-product bar.

Step 1: Drop unrated products first

WHERE rating IS NOT NULL runs before grouping, so every aggregate after it sees only products that were actually scored. One filter, and both figures inherit it.

Step 2: Count and average per category

GROUP BY category with COUNT(*) as rated_products and ROUND(AVG(rating), 1) as avg_rating. Because the unrated rows are already gone, COUNT(*) now means rated products, not products.

Step 3: Apply the three-product bar

HAVING COUNT(*) >= 3 cuts categories after aggregation. It is checked against the same rated population the count reports, so the bar and the number on screen can never disagree.

SELECT category, COUNT(*) AS rated_products, ROUND(AVG(rating), 1) AS avg_rating
FROM products
WHERE rating IS NOT NULL
GROUP BY category
HAVING COUNT(*) >= 3

No filter

COUNT(*) includes products with a NULL rating, so rated_products is inflated wherever a category holds an unscored product. AVG(rating) still looks right, which is exactly why nobody catches it.

Filter first

WHERE rating IS NOT NULL shrinks the population once. COUNT(*), AVG(rating) and the HAVING bar all measure the same rated products.

Common follow-up questions

• What if `rating` could also be 0 for products that were rated as a placeholder? (Tests whether the candidate separates a sentinel value from a real score and extends the filter.)
• How would you show categories that have no rated products at all, with a count of zero? (Tests moving from a filter that removes groups to conditional aggregation that keeps them.)
• What if you needed the median rating instead of the average? (Tests `PERCENTILE_CONT` and how ordered-set aggregates handle nulls.)