Average Rating by Category

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

Asked in

Yelp

Problem

Show the average customer rating of each category in the products catalog, best-loved first, for the PM's roadmap review.

Category ratings. Some shine, some don't.

Tables

products

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

Expected output

Worked solution and explanation

This is a mean over a column with holes in it, dressed up as a roadmap readout. Anyone can group products by category. What separates candidates is whether the denominator counts only the products that were actually rated. Thirteen of the 200 products have a NULL rating, spread over ten of the eleven categories. Divide by every product, or zero-fill the gaps, and ten categories read low: 'Books' slides from fourth to a tie for sixth, and the PM walks into the meeting flagging the wrong category.

Getting there

Step 1: Pick the grain: one row per category

The PM compares categories, so the output has one row per category value and nothing else. This seed has eleven, including 'Collectibles', which holds only two products.

Step 2: Let AVG(rating) own the denominator

AVG skips NULL inputs, so each category's mean is divided by its rated products only. That is the business meaning too: a product nobody has rated yet carries no verdict about its category. Writing SUM(rating) / COUNT(*) puts those unrated rows back in the denominator.

Step 3: Keep every product, in stock or not

A rating records what customers thought of a product, not whether it is on the shelf today. Every 'Music' product is out of stock, so an in_stock = 1 filter would erase that category from the readout entirely.

Step 4: Sort best-loved first

Order by avg_rating descending. The eleven averages all differ on this data, so that one key fixes the row order.

SELECT
    category,
    AVG(rating) AS avg_rating
FROM products
GROUP BY category
ORDER BY avg_rating DESC

Divides by every product

SUM(rating) / COUNT(*) or AVG(COALESCE(rating, 0)) counts the 13 unrated products. 'Books' lands at 2.665 instead of 2.961 and ties 'Electronics' for sixth.

Divides by rated products

AVG(rating) ignores the NULL ratings. 'Books' is 53.3 over its 18 rated products, 2.961, fourth on the list.

Common follow-up questions

• The PM wants an average shown only when at least five products in the category are rated. Where does that rule go, and what do you count? (Tests filtering groups on an aggregate, and counting `rating` rather than rows.)
• How would you report the share of each category's products that are still unrated? (Tests `COUNT(rating)` versus `COUNT(*)` as a deliberate measure.)
• 'Collectibles' tops the list on just two products. How would you keep a tiny category from looking like the best-loved? (Tests a minimum-volume threshold or a shrunk average.)