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
| Column | Type |
|---|---|
| product_id | int |
| product_name | varchar |
| category | varchar |
Primary key: product_id.
One row per catalogue product.
Table: SaleOrder
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| ordered_on | date |
| order_status | enum(delivered, shipped, cancelled) |
Primary key: order_id.
One row per order.
Table: OrderItem
| Column | Type |
|---|---|
| order_id | int |
| product_id | int |
| quantity | int |
Primary key: order_id, product_id.
One row per product on an order.
Examples
Example 1
Product
| product_id | product_name | category |
|---|---|---|
| 1 | Galaxy F15 | Mobiles |
| 2 | Cotton Kurta | Fashion |
| 3 | Mixer Grinder | Appliances |
| 4 | Face Serum | Beauty |
| 5 | Denim Jacket | Fashion |
SaleOrder
| order_id | customer_id | ordered_on | order_status |
|---|---|---|---|
| 1 | 101 | 2024-09-27 | delivered |
| 2 | 101 | 2024-10-06 | shipped |
| 3 | 102 | 2024-09-30 | delivered |
| 4 | 102 | 2024-10-01 | cancelled |
| 5 | 103 | 2024-09-26 | delivered |
| 6 | 103 | 2024-10-02 | delivered |
OrderItem
| order_id | product_id | quantity |
|---|---|---|
| 1 | 1 | 1 |
| 1 | 2 | 2 |
| 2 | 3 | 1 |
| 3 | 2 | 1 |
| 3 | 5 | 1 |
| 3 | 4 | 1 |
| 4 | 3 | 1 |
| 5 | 1 | 1 |
| 6 | 2 | 1 |
| 6 | 4 | 1 |
Output
| customer_id | categories |
|---|---|
| 101 | 3 |
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_idAnother 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 →