Myntra: Wishlisted Styles the Shopper Has Not Bought Yet

Easy
802.

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

ColumnType
user_idint
style_idint
added_ondate

Primary key (together): user_id, style_id.

One row per style a shopper has saved to their wishlist.

Table: OrderLine

ColumnTypeValues
order_line_idint
user_idint
style_idint
sizevarchar
ordered_ondate
statusenumdelivered, returned, cancelled

Primary key: order_line_id.

One row per item bought, in one size.

Example

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

Wishlist

user_idstyle_idadded_on
1102024-05-01
1112024-05-03
2102024-05-04
2122024-05-10
3112024-05-12

OrderLine

order_line_iduser_idstyle_idsizeordered_onstatus
1110M2024-05-20delivered
2210L2024-05-21cancelled
3212S2024-04-01returned
4310M2024-05-22delivered

Output

user_idstyle_id
111
210
311
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.

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

ColumnType
user_idint
style_idint
added_ondate

Primary key: user_id, style_id.

One row per style a shopper has saved to their wishlist.

Table: OrderLine

ColumnType
order_line_idint
user_idint
style_idint
sizevarchar
ordered_ondate
statusenum(delivered, returned, cancelled)

Primary key: order_line_id.

One row per item bought, in one size.

Examples

Example 1

Wishlist

user_idstyle_idadded_on
1102024-05-01
1112024-05-03
2102024-05-04
2122024-05-10
3112024-05-12

OrderLine

order_line_iduser_idstyle_idsizeordered_onstatus
1110M2024-05-20delivered
2210L2024-05-21cancelled
3212S2024-04-01returned
4310M2024-05-22delivered

Output

user_idstyle_id
111
210
311

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 NULL

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