Flipkart: Sale Shoppers Who Bought From Three Categories

Medium
662.

Flipkart: Sale Shoppers Who Bought From Three Categories

MediumJoinsAggregation

MySQL

The growth team wants to thank customers who explored the store widely during Big Billion Days (2024-09-27 to 2024-10-06, inclusive).

Consider orders placed in the sale window whose order_status is not cancelled. Return every customer who bought products from at least 3 different categories across those orders, with customer_id and categories (the number of distinct categories). Order by categories descending, then customer_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
categoryvarchar

Primary key: product_id.

One row per catalogue product.

Table: SaleOrder

ColumnTypeValues
order_idint
customer_idint
ordered_ondate
order_statusenumdelivered, shipped, cancelled

Primary key: order_id.

One row per order.

Table: OrderItem

ColumnType
order_idint
product_idint
quantityint

Primary key (together): order_id, product_id.

One row per product on an order.

Example

The rows must come back in the order shown.

Product

product_idproduct_namecategory
1Galaxy F15Mobiles
2Cotton KurtaFashion
3Mixer GrinderAppliances
4Face SerumBeauty
5Denim JacketFashion

SaleOrder

order_idcustomer_idordered_onorder_status
11012024-09-27delivered
21012024-10-06shipped
31022024-09-30delivered
41022024-10-01cancelled
51032024-09-26delivered
61032024-10-02delivered

OrderItem

order_idproduct_idquantity
111
122
231
321
351
341
431
511
621
641

Output

customer_idcategories
1013
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: Sale Shoppers Who Bought From Three Categories

The growth team wants to thank customers who explored the store widely during Big Billion Days (2024-09-27 to 2024-10-06, inclusive).

  • Difficulty: Medium
  • Topics: Joins, Aggregation
  • Dialect: MySQL
  • Problem: #662

Problem statement

The growth team wants to thank customers who explored the store widely during Big Billion Days (2024-09-27 to 2024-10-06, inclusive).

Consider orders placed in the sale window whose order_status is not cancelled. Return every customer who bought products from at least 3 different categories across those orders, with customer_id and categories (the number of distinct categories). Order by categories descending, then customer_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
categoryvarchar

Primary key: product_id.

One row per catalogue product.

Table: SaleOrder

ColumnType
order_idint
customer_idint
ordered_ondate
order_statusenum(delivered, shipped, cancelled)

Primary key: order_id.

One row per order.

Table: OrderItem

ColumnType
order_idint
product_idint
quantityint

Primary key: order_id, product_id.

One row per product on an order.

Examples

Example 1

Product

product_idproduct_namecategory
1Galaxy F15Mobiles
2Cotton KurtaFashion
3Mixer GrinderAppliances
4Face SerumBeauty
5Denim JacketFashion

SaleOrder

order_idcustomer_idordered_onorder_status
11012024-09-27delivered
21012024-10-06shipped
31022024-09-30delivered
41022024-10-01cancelled
51032024-09-26delivered
61032024-10-02delivered

OrderItem

order_idproduct_idquantity
111
122
231
321
351
341
431
511
621
641

Output

customer_idcategories
1013

How to solve Flipkart: Sale Shoppers Who Bought From Three Categories

The customer is on the order and the category on the product, so the query walks SaleOrder → OrderItem → Product. The WHERE clause keeps orders inside the inclusive sale window that were not cancelled; a cancelled order's items must not count even if they are the only Appliances the customer touched.

After grouping by customer, the joined rows contain one row per item, so a customer who bought two Fashion items in one order, or Fashion in two orders, has Fashion several times. COUNT(DISTINCT p.category) counts each category once, and HAVING … >= 3 keeps the wide explorers.

The alternative first reduces the rows to distinct (customer, category) pairs in a derived table and then counts pairs per customer with a plain COUNT — the same idea in two steps, which is also handy when you later want to list the categories. Both cost a join over the sale's items plus a grouping.

Reference solution (MySQL)

SELECT o.customer_id, COUNT(DISTINCT p.category) AS categories
FROM SaleOrder o
JOIN OrderItem i ON i.order_id = o.order_id
JOIN Product p ON p.product_id = i.product_id
WHERE o.order_status <> 'cancelled'
  AND o.ordered_on BETWEEN '2024-09-27' AND '2024-10-06'
GROUP BY o.customer_id
HAVING COUNT(DISTINCT p.category) >= 3
ORDER BY categories DESC, o.customer_id

Another way

SELECT customer_id, COUNT(*) AS categories
FROM (SELECT DISTINCT o.customer_id, p.category
      FROM SaleOrder o, OrderItem i, Product p
      WHERE i.order_id = o.order_id AND p.product_id = i.product_id
        AND o.order_status IN ('delivered', 'shipped')
        AND o.ordered_on >= '2024-09-27' AND o.ordered_on <= '2024-10-06') cc
GROUP BY customer_id
HAVING COUNT(*) >= 3
ORDER BY categories DESC, customer_id

← Flipkart: F-Assured Sellers Missing the 24-Hour Dispatch SLA · Flipkart: Top Three Brands per Category Plus an Others Line →