Flipkart: Certified Buyer Reviews and Their Rating per Product
Flipkart marks a review Certified Buyer when the reviewer had actually received the product before writing it.
- Difficulty: Medium
- Topics: Subqueries, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #660
Problem statement
Flipkart marks a review Certified Buyer when the reviewer had actually received the product before writing it. The trust-and-safety team wants to see how much of each product's rating comes from certified buyers.
A review is certified when the same customer has an order of the same product with delivered_on on or before reviewed_on (orders not yet delivered have delivered_on NULL and never certify). For every product with at least one review, return product_name, reviews (all its reviews), certified_reviews and certified_avg_rating (the average rating of its certified reviews rounded to 2 decimal places, NULL if it has none). A customer may have ordered the same product more than once; a review still counts once. Order by product_id.
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 catalogue product.
Table: ProductOrder
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| product_id | int |
| delivered_on | date |
Primary key: order_id.
One row per order of one product; delivered_on is NULL while the parcel has not been delivered.
Table: Review
| Column | Type |
|---|---|
| review_id | int |
| product_id | int |
| customer_id | int |
| rating | int |
| reviewed_on | date |
Primary key: review_id.
One row per review; rating is 1 to 5 stars and product_id always names a product.
Examples
Example 1
Product
| product_id | product_name |
|---|---|
| 1 | Nova X5 |
| 2 | Steel Bottle |
| 3 | Face Serum |
ProductOrder
| order_id | customer_id | product_id | delivered_on |
|---|---|---|---|
| 1 | 101 | 1 | 2024-10-03 |
| 2 | 101 | 1 | 2024-10-10 |
| 3 | 102 | 1 | NULL |
| 4 | 103 | 1 | 2024-10-12 |
| 5 | 104 | 2 | 2024-10-05 |
| 6 | 105 | 3 | 2024-10-01 |
Review
| review_id | product_id | customer_id | rating | reviewed_on |
|---|---|---|---|---|
| 1 | 1 | 101 | 5 | 2024-10-04 |
| 2 | 1 | 102 | 1 | 2024-10-06 |
| 3 | 1 | 103 | 4 | 2024-10-11 |
| 4 | 1 | 106 | 2 | 2024-10-08 |
| 5 | 2 | 104 | 3 | 2024-10-05 |
| 6 | 2 | 105 | 1 | 2024-10-06 |
Output
| product_name | reviews | certified_reviews | certified_avg_rating |
|---|---|---|---|
| Nova X5 | 4 | 1 | 5 |
| Steel Bottle | 2 | 1 | 3 |
How to solve Flipkart: Certified Buyer Reviews and Their Rating per Product
The trap is the join. A customer who ordered the same phone twice has two order rows, so joining reviews to orders would count that customer's review twice and weight its rating double. The question per review is yes or no — is there an order of this product by this customer delivered by the review date — which is exactly what a correlated EXISTS answers without multiplying rows. The flag is computed per review in a CTE, and only then are the reviews grouped by product.
An undelivered order has delivered_on NULL, and NULL <= reviewed_on is not true, so it never certifies. A delivery on the review day itself does certify, because the comparison is <=.
In the aggregate, COUNT(*) counts all reviews, SUM(certified) the certified ones, and AVG(CASE WHEN certified = 1 THEN rating END) averages only those — a product with none gets NULL, not 0. The alternative first reduces the orders to each customer's earliest delivery of each product, which is all that matters, and LEFT JOINs that one row per pair, so no review can be repeated. Both are a pass over the reviews with an index lookup into the orders.
Reference solution (MySQL)
WITH flagged AS (
SELECT r.product_id, r.rating,
CASE WHEN EXISTS (SELECT 1 FROM ProductOrder o
WHERE o.customer_id = r.customer_id AND o.product_id = r.product_id
AND o.delivered_on <= r.reviewed_on) THEN 1 ELSE 0 END AS certified
FROM Review r
)
SELECT p.product_name,
COUNT(*) AS reviews,
SUM(f.certified) AS certified_reviews,
ROUND(AVG(CASE WHEN f.certified = 1 THEN f.rating END), 2) AS certified_avg_rating
FROM flagged f
JOIN Product p ON p.product_id = f.product_id
GROUP BY p.product_id, p.product_name
ORDER BY p.product_idAnother way
WITH first_delivery AS (
SELECT customer_id, product_id, MIN(delivered_on) AS first_on
FROM ProductOrder WHERE delivered_on IS NOT NULL
GROUP BY customer_id, product_id
)
SELECT p.product_name,
COUNT(*) AS reviews,
COUNT(CASE WHEN d.first_on <= r.reviewed_on THEN 1 END) AS certified_reviews,
ROUND(AVG(IF(d.first_on <= r.reviewed_on, r.rating, NULL)), 2) AS certified_avg_rating
FROM Review r
JOIN Product p ON p.product_id = r.product_id
LEFT JOIN first_delivery d ON d.customer_id = r.customer_id AND d.product_id = r.product_id
GROUP BY p.product_id, p.product_name
ORDER BY p.product_id← Flipkart: Products Rated Lower After Big Billion Days · Flipkart: F-Assured Sellers Missing the 24-Hour Dispatch SLA →