Amazon: Star Rating Breakdown for Each Product — SQL Problem
The customer reviews team draws the familiar five-bar histogram on every product page and wants the counts behind it from one query.
- Difficulty: Medium
- Topics: Aggregation, Conditional Logic, Joins
- Dialect: MySQL
- Problem: #582
Problem statement
The customer reviews team draws the familiar five-bar histogram on every product page and wants the counts behind it from one query.
For every product — including products with no published reviews — return asin, five_star, four_star, three_star, two_star, one_star (the number of published reviews with that many stars) and verified_reviews (published reviews from a verified purchase). Removed reviews count nowhere. A product with no published reviews shows 0 in every count, not NULL. Return the rows in any order.
A practice scenario set at Amazon: the tables and data are invented, and this is not a question from, or endorsed by, Amazon.
Tables
Table: Product
| Column | Type |
|---|---|
| asin | varchar |
| title | varchar |
Primary key: asin.
One row per product in the catalogue.
Table: Review
| Column | Type |
|---|---|
| review_id | int |
| asin | varchar |
| stars | int |
| verified | bool |
| status | enum(published, removed) |
Primary key: review_id.
One row per customer review; stars is 1 to 5 and asin always names a product.
Examples
Example 1
Product
| asin | title |
|---|---|
| B0FAN00001 | Tower fan 48 inch |
| B0MUG00002 | Steel travel mug |
| B0YOGA0003 | Yoga mat 6 mm |
Review
| review_id | asin | stars | verified | status |
|---|---|---|---|---|
| 1 | B0FAN00001 | 5 | 1 | published |
| 2 | B0FAN00001 | 5 | 0 | published |
| 3 | B0FAN00001 | 2 | 1 | published |
| 4 | B0FAN00001 | 1 | 1 | removed |
| 5 | B0MUG00002 | 4 | 1 | published |
| 6 | B0MUG00002 | 3 | 0 | published |
| 7 | B0YOGA0003 | 1 | 0 | removed |
Output
| asin | five_star | four_star | three_star | two_star | one_star | verified_reviews |
|---|---|---|---|---|---|---|
| B0FAN00001 | 2 | 0 | 0 | 1 | 0 | 2 |
| B0MUG00002 | 0 | 1 | 1 | 0 | 0 | 1 |
| B0YOGA0003 | 0 | 0 | 0 | 0 | 0 | 0 |
How to solve Amazon: Star Rating Breakdown for Each Product
This is a pivot: one output row per product, one column per star value. A conditional aggregate does it in one pass — COUNT(CASE WHEN r.stars = 5 THEN 1 END) counts the rows where the CASE is not NULL, i.e. the five-star ones, and so on for each column, plus one for verified purchases.
The trap is the products with nothing to count. A LEFT JOIN from Product keeps them, with NULL review columns. The status = 'published' condition must sit in the ON clause: in WHERE it would throw away the NULL-extended rows and with them every product that has only removed reviews or none. And COUNT(expression) returns 0 for a group where the expression is always NULL, which gives the required zeros for free — SUM(CASE … THEN 1 ELSE 0 END) would also give 0 here, because the joined NULL row still exists, but SUM over a product with no rows at all would be NULL.
The alternative aggregates the published reviews first in a derived table and LEFT JOINs that to Product, so it must turn the missing rows into zeros with COALESCE. Both are a single scan of the reviews.
Reference solution (MySQL)
SELECT p.asin,
COUNT(CASE WHEN r.stars = 5 THEN 1 END) AS five_star,
COUNT(CASE WHEN r.stars = 4 THEN 1 END) AS four_star,
COUNT(CASE WHEN r.stars = 3 THEN 1 END) AS three_star,
COUNT(CASE WHEN r.stars = 2 THEN 1 END) AS two_star,
COUNT(CASE WHEN r.stars = 1 THEN 1 END) AS one_star,
COUNT(CASE WHEN r.verified = 1 THEN 1 END) AS verified_reviews
FROM Product p
LEFT JOIN Review r ON r.asin = p.asin AND r.status = 'published'
GROUP BY p.asinAnother way
SELECT p.asin, COALESCE(t.f5, 0) AS five_star, COALESCE(t.f4, 0) AS four_star, COALESCE(t.f3, 0) AS three_star,
COALESCE(t.f2, 0) AS two_star, COALESCE(t.f1, 0) AS one_star, COALESCE(t.v, 0) AS verified_reviews
FROM Product p LEFT JOIN (
SELECT asin, SUM(IF(stars = 5, 1, 0)) AS f5, SUM(IF(stars = 4, 1, 0)) AS f4, SUM(IF(stars = 3, 1, 0)) AS f3,
SUM(IF(stars = 2, 1, 0)) AS f2, SUM(IF(stars = 1, 1, 0)) AS f1, SUM(verified) AS v
FROM Review WHERE status = 'published' GROUP BY asin) t ON t.asin = p.asin← Amazon: Buy Box Winner for Each ASIN · Amazon: Echo Buyers Who Added a Smart Plug Within 60 Days →