Amazon: Five-Star Review Bursts From Brand-New Accounts

Hard
594.

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

ColumnType
customer_idint
created_ondate

Primary key: customer_id.

One row per shopper account.

Table: ProductReview

ColumnType
review_idint
asinvarchar
customer_idint
starsint
posted_atdatetime

Primary key: review_id.

One row per review; a customer reviews a product at most once, and customer_id always names a customer.

Example

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

Customer

customer_idcreated_on
12025-03-01
22025-03-02
32025-03-03
42024-06-01
52025-02-04

ProductReview

review_idasincustomer_idstarsposted_at
1B0SERUM001152025-03-05 10:00:00
2B0SERUM001252025-03-05 22:00:00
3B0SERUM001352025-03-06 10:00:00
4B0SERUM001452025-03-05 12:00:00
5B0SERUM001552025-03-06 09:00:00
6B0CHARGER2152025-03-10 08:00:00
7B0CHARGER2252025-03-10 09:00:00
8B0CHARGER2352025-03-11 09:00:01
9B0CHARGER2542025-03-10 10:00:00

Output

asinburst_size
B0SERUM0013
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: 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

ColumnType
customer_idint
created_ondate

Primary key: customer_id.

One row per shopper account.

Table: ProductReview

ColumnType
review_idint
asinvarchar
customer_idint
starsint
posted_atdatetime

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_idcreated_on
12025-03-01
22025-03-02
32025-03-03
42024-06-01
52025-02-04

ProductReview

review_idasincustomer_idstarsposted_at
1B0SERUM001152025-03-05 10:00:00
2B0SERUM001252025-03-05 22:00:00
3B0SERUM001352025-03-06 10:00:00
4B0SERUM001452025-03-05 12:00:00
5B0SERUM001552025-03-06 09:00:00
6B0CHARGER2152025-03-10 08:00:00
7B0CHARGER2252025-03-10 09:00:00
8B0CHARGER2352025-03-11 09:00:01
9B0CHARGER2542025-03-10 10:00:00

Output

asinburst_size
B0SERUM0013

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) >= 3

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