Myntra: Wishlisted Styles the Shopper Has Not Bought Yet
Before a sale, the CRM team sends each shopper a reminder about the styles still sitting in their wishlist.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #802
Problem statement
Before a sale, the CRM team sends each shopper a reminder about the styles still sitting in their wishlist. A style the shopper has already bought should not be in that reminder.
Return user_id and style_id for every wishlist entry where that same shopper has no order line of that style with a status other than cancelled — a delivered or returned purchase counts as bought, at any date. Another shopper's purchase does not matter. Return the rows in any order.
A practice scenario set at Myntra: the tables and data are invented, and this is not a question from, or endorsed by, Myntra.
Tables
Table: Wishlist
| Column | Type |
|---|---|
| user_id | int |
| style_id | int |
| added_on | date |
Primary key: user_id, style_id.
One row per style a shopper has saved to their wishlist.
Table: OrderLine
| Column | Type |
|---|---|
| order_line_id | int |
| user_id | int |
| style_id | int |
| size | varchar |
| ordered_on | date |
| status | enum(delivered, returned, cancelled) |
Primary key: order_line_id.
One row per item bought, in one size.
Examples
Example 1
Wishlist
| user_id | style_id | added_on |
|---|---|---|
| 1 | 10 | 2024-05-01 |
| 1 | 11 | 2024-05-03 |
| 2 | 10 | 2024-05-04 |
| 2 | 12 | 2024-05-10 |
| 3 | 11 | 2024-05-12 |
OrderLine
| order_line_id | user_id | style_id | size | ordered_on | status |
|---|---|---|---|---|---|
| 1 | 1 | 10 | M | 2024-05-20 | delivered |
| 2 | 2 | 10 | L | 2024-05-21 | cancelled |
| 3 | 2 | 12 | S | 2024-04-01 | returned |
| 4 | 3 | 10 | M | 2024-05-22 | delivered |
Output
| user_id | style_id |
|---|---|
| 1 | 11 |
| 2 | 10 |
| 3 | 11 |
How to solve Myntra: Wishlisted Styles the Shopper Has Not Bought Yet
Each wishlist row is a (shopper, style) pair, and it stays in the reminder unless that exact pair also appears among the shopper's real purchases. That is an anti join on two columns: the correlated NOT EXISTS looks for an order line with the same user_id and the same style_id whose status is not cancelled, and keeps the wishlist row only when there is none.
Matching on style_id alone would be the bug — shopper 3 buying style 10 must not remove style 10 from shopper 2's reminder. And the status test sits inside the match, so a cancelled order is treated as if it never happened, while a returned one still counts as bought, as the statement says. The purchase date does not matter: a style bought before it was wishlisted is still bought.
The LEFT JOIN alternative carries the same conditions in ON and keeps the rows that found no partner. EXCEPT removes the bought pairs from the wishlisted ones as sets — safe here because the wishlist's key already makes its pairs distinct. All run in time proportional to the two tables with an index on (user_id, style_id).
Reference solution (MySQL)
SELECT w.user_id, w.style_id
FROM Wishlist w
WHERE NOT EXISTS (
SELECT 1 FROM OrderLine o
WHERE o.user_id = w.user_id AND o.style_id = w.style_id AND o.status <> 'cancelled'
)Another way
SELECT w.user_id, w.style_id
FROM Wishlist w
LEFT JOIN OrderLine o ON o.user_id = w.user_id AND o.style_id = w.style_id AND o.status <> 'cancelled'
WHERE o.order_line_id IS NULLAnother way
SELECT user_id, style_id FROM Wishlist EXCEPT SELECT user_id, style_id FROM OrderLine WHERE status <> 'cancelled'← Myntra: Product Display Names From Brand, Colour and Article · Myntra: Insider Points Earned on Each Delivered Order →