Amazon: Star Rating Breakdown for Each Product

Medium
582.

Amazon: Star Rating Breakdown for Each Product

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

ColumnType
asinvarchar
titlevarchar

Primary key: asin.

One row per product in the catalogue.

Table: Review

ColumnTypeValues
review_idint
asinvarchar
starsint
verifiedbool
statusenumpublished, removed

Primary key: review_id.

One row per customer review; stars is 1 to 5 and asin always names a product.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Product

asintitle
B0FAN00001Tower fan 48 inch
B0MUG00002Steel travel mug
B0YOGA0003Yoga mat 6 mm

Review

review_idasinstarsverifiedstatus
1B0FAN0000151published
2B0FAN0000150published
3B0FAN0000121published
4B0FAN0000111removed
5B0MUG0000241published
6B0MUG0000230published
7B0YOGA000310removed

Output

asinfive_starfour_starthree_startwo_starone_starverified_reviews
B0FAN00001200102
B0MUG00002011001
B0YOGA0003000000
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
asinvarchar
titlevarchar

Primary key: asin.

One row per product in the catalogue.

Table: Review

ColumnType
review_idint
asinvarchar
starsint
verifiedbool
statusenum(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

asintitle
B0FAN00001Tower fan 48 inch
B0MUG00002Steel travel mug
B0YOGA0003Yoga mat 6 mm

Review

review_idasinstarsverifiedstatus
1B0FAN0000151published
2B0FAN0000150published
3B0FAN0000121published
4B0FAN0000111removed
5B0MUG0000241published
6B0MUG0000230published
7B0YOGA000310removed

Output

asinfive_starfour_starthree_startwo_starone_starverified_reviews
B0FAN00001200102
B0MUG00002011001
B0YOGA0003000000

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.asin

Another 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 →