Flipkart: Products Rated Lower After Big Billion Days

Medium
659.

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

ColumnType
product_idint
product_namevarchar

Primary key: product_id.

One row per phone model.

Table: ProductReview

ColumnType
review_idint
product_idint
ratingint
reviewed_ondate

Primary key: review_id.

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

Example

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

Product

product_idproduct_name
1Nova X5
2Pixelon 8a
3Redstar 12

ProductReview

review_idproduct_idratingreviewed_on
1152024-09-10
2142024-09-26
3112024-10-01
4142024-10-07
5132024-10-20
6242024-09-01
7242024-09-15
8252024-10-12
9232024-10-30
10352024-09-20
2 more rows

Output

product_namebefore_avgafter_avg
Nova X54.53.5
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: 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

ColumnType
product_idint
product_namevarchar

Primary key: product_id.

One row per phone model.

Table: ProductReview

ColumnType
review_idint
product_idint
ratingint
reviewed_ondate

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_idproduct_name
1Nova X5
2Pixelon 8a
3Redstar 12

ProductReview

review_idproduct_idratingreviewed_on
1152024-09-10
2142024-09-26
3112024-10-01
4142024-10-07
5132024-10-20
6242024-09-01
7242024-09-15
8252024-10-12
9232024-10-30
10352024-09-20
11322024-10-15
12322024-10-16

Output

product_namebefore_avgafter_avg
Nova X54.53.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 →