Flipkart: Products Rated Lower After Big Billion Days
The phones category manager suspects that some models sold in a rush during Big Billion Days (2024-09-27 to 2024-10-06) disappointed buyers.
- Difficulty: Medium
- Topics: Joins, Aggregation, Dates, Conditional Logic
- Dialect: MySQL
- Problem: #659
Problem statement
The phones category manager suspects that some models sold in a rush during Big Billion Days (2024-09-27 to 2024-10-06) disappointed buyers. Reviews written before the sale (before 2024-09-27) are compared with reviews written after it (after 2024-10-06); reviews written during the sale are ignored.
Return the products with at least 2 reviews in each period whose average rating after the sale is strictly lower than before it. Return product_name, before_avg and after_avg, each average rounded to 2 decimal places, in any order.
A practice scenario set at Flipkart: the tables and data are invented, and this is not a question from, or endorsed by, Flipkart.
Tables
Table: Product
| Column | Type |
|---|---|
| product_id | int |
| product_name | varchar |
Primary key: product_id.
One row per phone model.
Table: ProductReview
| Column | Type |
|---|---|
| review_id | int |
| product_id | int |
| rating | int |
| reviewed_on | date |
Primary key: review_id.
One row per review; rating is 1 to 5 stars. product_id always names a product.
Examples
Example 1
Product
| product_id | product_name |
|---|---|
| 1 | Nova X5 |
| 2 | Pixelon 8a |
| 3 | Redstar 12 |
ProductReview
| review_id | product_id | rating | reviewed_on |
|---|---|---|---|
| 1 | 1 | 5 | 2024-09-10 |
| 2 | 1 | 4 | 2024-09-26 |
| 3 | 1 | 1 | 2024-10-01 |
| 4 | 1 | 4 | 2024-10-07 |
| 5 | 1 | 3 | 2024-10-20 |
| 6 | 2 | 4 | 2024-09-01 |
| 7 | 2 | 4 | 2024-09-15 |
| 8 | 2 | 5 | 2024-10-12 |
| 9 | 2 | 3 | 2024-10-30 |
| 10 | 3 | 5 | 2024-09-20 |
| 11 | 3 | 2 | 2024-10-15 |
| 12 | 3 | 2 | 2024-10-16 |
Output
| product_name | before_avg | after_avg |
|---|---|---|
| Nova X5 | 4.5 | 3.5 |
How to solve Flipkart: Products Rated Lower After Big Billion Days
Each product needs two averages over two disjoint sets of its reviews. Conditional aggregation gets both in one GROUP BY: AVG(CASE WHEN reviewed_on < '2024-09-27' THEN rating END) turns every review outside the period into NULL, and AVG ignores NULLs, so it averages only the pre-sale reviews. The CASE has no ELSE on purpose — an ELSE 0 would drag the average down. Reviews written during the sale match neither CASE and so count nowhere.
The HAVING clause applies all three conditions on the groups: at least two reviews before (a SUM of a 0/1 flag), at least two after, and the after-average strictly below the before-average. The comparison uses the unrounded averages, so two values that round to the same figure are still told apart correctly.
The alternative builds one derived table per period and inner-joins them, which drops products missing a period automatically. Both scan the reviews once or twice; an index on (product_id, reviewed_on) helps either.
Reference solution (MySQL)
SELECT p.product_name,
ROUND(AVG(CASE WHEN r.reviewed_on < '2024-09-27' THEN r.rating END), 2) AS before_avg,
ROUND(AVG(CASE WHEN r.reviewed_on > '2024-10-06' THEN r.rating END), 2) AS after_avg
FROM Product p
JOIN ProductReview r ON r.product_id = p.product_id
GROUP BY p.product_id, p.product_name
HAVING SUM(CASE WHEN r.reviewed_on < '2024-09-27' THEN 1 ELSE 0 END) >= 2
AND SUM(CASE WHEN r.reviewed_on > '2024-10-06' THEN 1 ELSE 0 END) >= 2
AND AVG(CASE WHEN r.reviewed_on > '2024-10-06' THEN r.rating END) < AVG(CASE WHEN r.reviewed_on < '2024-09-27' THEN r.rating END)Another way
SELECT p.product_name, ROUND(b.avg_rating, 2) AS before_avg, ROUND(a.avg_rating, 2) AS after_avg
FROM Product p
JOIN (SELECT product_id, AVG(rating) AS avg_rating, COUNT(*) AS n FROM ProductReview WHERE reviewed_on < '2024-09-27' GROUP BY product_id) b
ON b.product_id = p.product_id
JOIN (SELECT product_id, AVG(rating) AS avg_rating, COUNT(*) AS n FROM ProductReview WHERE reviewed_on > '2024-10-06' GROUP BY product_id) a
ON a.product_id = p.product_id
WHERE b.n >= 2 AND a.n >= 2 AND a.avg_rating < b.avg_rating← Flipkart: Pincodes Where COD Parcels Keep Coming Back · Flipkart: Certified Buyer Reviews and Their Rating per Product →