Flipkart: Certified Buyer Reviews and Their Rating per Product

Medium
660.

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

ColumnType
product_idint
product_namevarchar

Primary key: product_id.

One row per catalogue product.

Table: ProductOrder

ColumnType
order_idint
customer_idint
product_idint
delivered_ondate

Primary key: order_id.

One row per order of one product; delivered_on is NULL while the parcel has not been delivered.

Table: Review

ColumnType
review_idint
product_idint
customer_idint
ratingint
reviewed_ondate

Primary key: review_id.

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

Example

The rows must come back in the order shown.

Product

product_idproduct_name
1Nova X5
2Steel Bottle
3Face Serum

ProductOrder

order_idcustomer_idproduct_iddelivered_on
110112024-10-03
210112024-10-10
31021NULL
410312024-10-12
510422024-10-05
610532024-10-01

Review

review_idproduct_idcustomer_idratingreviewed_on
1110152024-10-04
2110212024-10-06
3110342024-10-11
4110622024-10-08
5210432024-10-05
6210512024-10-06

Output

product_namereviewscertified_reviewscertified_avg_rating
Nova X5415
Steel Bottle213
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.

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

ColumnType
product_idint
product_namevarchar

Primary key: product_id.

One row per catalogue product.

Table: ProductOrder

ColumnType
order_idint
customer_idint
product_idint
delivered_ondate

Primary key: order_id.

One row per order of one product; delivered_on is NULL while the parcel has not been delivered.

Table: Review

ColumnType
review_idint
product_idint
customer_idint
ratingint
reviewed_ondate

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_idproduct_name
1Nova X5
2Steel Bottle
3Face Serum

ProductOrder

order_idcustomer_idproduct_iddelivered_on
110112024-10-03
210112024-10-10
31021NULL
410312024-10-12
510422024-10-05
610532024-10-01

Review

review_idproduct_idcustomer_idratingreviewed_on
1110152024-10-04
2110212024-10-06
3110342024-10-11
4110622024-10-08
5210432024-10-05
6210512024-10-06

Output

product_namereviewscertified_reviewscertified_avg_rating
Nova X5415
Steel Bottle213

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_id

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