Amazon: Five-Star Review Bursts From Brand-New Accounts
The review-integrity team hunts for paid-review campaigns, which tend to look like a cluster of five-star reviews from freshly created accounts.
- Difficulty: Hard
- Topics: Joins, Dates, Aggregation, Subqueries
- Dialect: MySQL
- Problem: #594
Problem statement
The review-integrity team hunts for paid-review campaigns, which tend to look like a cluster of five-star reviews from freshly created accounts. A review is suspect when it has 5 stars and was posted fewer than 30 days after the reviewer's account was created (compare calendar days, DATEDIFF(posted_at, created_on) < 30).
For each product, find the largest number of suspect reviews posted within any 24-hour span (a review posted exactly 24 hours after the first one still counts as inside it). Return asin and burst_size for every product whose largest burst is at least 3; 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: Customer
| Column | Type |
|---|---|
| customer_id | int |
| created_on | date |
Primary key: customer_id.
One row per shopper account.
Table: ProductReview
| Column | Type |
|---|---|
| review_id | int |
| asin | varchar |
| customer_id | int |
| stars | int |
| posted_at | datetime |
Primary key: review_id.
One row per review; a customer reviews a product at most once, and customer_id always names a customer.
Examples
Example 1
Customer
| customer_id | created_on |
|---|---|
| 1 | 2025-03-01 |
| 2 | 2025-03-02 |
| 3 | 2025-03-03 |
| 4 | 2024-06-01 |
| 5 | 2025-02-04 |
ProductReview
| review_id | asin | customer_id | stars | posted_at |
|---|---|---|---|---|
| 1 | B0SERUM001 | 1 | 5 | 2025-03-05 10:00:00 |
| 2 | B0SERUM001 | 2 | 5 | 2025-03-05 22:00:00 |
| 3 | B0SERUM001 | 3 | 5 | 2025-03-06 10:00:00 |
| 4 | B0SERUM001 | 4 | 5 | 2025-03-05 12:00:00 |
| 5 | B0SERUM001 | 5 | 5 | 2025-03-06 09:00:00 |
| 6 | B0CHARGER2 | 1 | 5 | 2025-03-10 08:00:00 |
| 7 | B0CHARGER2 | 2 | 5 | 2025-03-10 09:00:00 |
| 8 | B0CHARGER2 | 3 | 5 | 2025-03-11 09:00:01 |
| 9 | B0CHARGER2 | 5 | 4 | 2025-03-10 10:00:00 |
Output
| asin | burst_size |
|---|---|
| B0SERUM001 | 3 |
How to solve Amazon: Five-Star Review Bursts From Brand-New Accounts
Start by narrowing the data: a review is suspect when it has five stars and the account was under 30 days old when it was posted. DATEDIFF compares calendar days and ignores the time of day, so a review on day 29 qualifies and one on day 30 does not.
The key observation for the sliding window is that a busiest 24-hour span can always be shifted to begin at a suspect review without losing any review from it. So it suffices to try every suspect review as a span start: self-join the suspect set to itself on the same asin with b.posted_at between a.posted_at and a.posted_at + 24 hours (both ends inclusive), and count the matches per starting review — the start counts itself. The product's burst size is the MAX of those counts, and HAVING keeps products reaching 3.
The alternative does the same count as a correlated subquery per suspect review, using TIMESTAMPDIFF(SECOND, …) BETWEEN 0 AND 86400 for the span. The self join is quadratic in the suspect reviews of a product, which is small in practice; a window function over a numeric timestamp with a RANGE frame is the scalable version on engines that allow it.
Reference solution (MySQL)
WITH suspect AS (
SELECT r.review_id, r.asin, r.posted_at
FROM ProductReview r
JOIN Customer c ON c.customer_id = r.customer_id
WHERE r.stars = 5 AND DATEDIFF(r.posted_at, c.created_on) < 30
), bursts AS (
SELECT a.review_id, a.asin, COUNT(*) AS in_span
FROM suspect a
JOIN suspect b
ON b.asin = a.asin
AND b.posted_at >= a.posted_at
AND b.posted_at <= DATE_ADD(a.posted_at, INTERVAL 24 HOUR)
GROUP BY a.review_id, a.asin
)
SELECT asin, MAX(in_span) AS burst_size
FROM bursts
GROUP BY asin
HAVING MAX(in_span) >= 3Another way
SELECT asin, burst_size FROM (
SELECT s.asin, MAX((SELECT COUNT(*) FROM ProductReview r2 JOIN Customer c2 ON c2.customer_id = r2.customer_id
WHERE r2.asin = s.asin AND r2.stars = 5 AND DATEDIFF(r2.posted_at, c2.created_on) < 30
AND TIMESTAMPDIFF(SECOND, s.posted_at, r2.posted_at) BETWEEN 0 AND 86400)) AS burst_size
FROM ProductReview s JOIN Customer c ON c.customer_id = s.customer_id
WHERE s.stars = 5 AND DATEDIFF(s.posted_at, c.created_on) < 30
GROUP BY s.asin) t
WHERE burst_size >= 3← Amazon: Package Dwell Time at Each Facility Visit · Amazon: AWS Promotional Credits Applied Month by Month →