Myntra: Wishlist-to-Purchase Conversion by Article Type
The growth team wants to know which article types turn wishlist saves into sales.
- Difficulty: Medium
- Topics: Subqueries, Aggregation, Joins
- Dialect: MySQL
- Problem: #810
Problem statement
The growth team wants to know which article types turn wishlist saves into sales.
A wishlist entry converts when the same shopper has an order line of that same style with a status other than cancelled and an ordered_on on or after the entry's added_on. Several such lines still count as one conversion. For every article_type with at least one wishlist entry, return article_type, wishlisted (entries), converted (entries that converted) and conversion_pct = 100 * converted / wishlisted rounded to 1 decimal place, ordered by conversion_pct descending, then article_type ascending.
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: Style
| Column | Type |
|---|---|
| style_id | int |
| article_type | varchar |
Primary key: style_id.
One row per style.
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 saved.
Table: OrderLine
| Column | Type |
|---|---|
| order_line_id | int |
| user_id | int |
| style_id | int |
| ordered_on | date |
| status | enum(delivered, returned, cancelled) |
Primary key: order_line_id.
One row per item bought.
Examples
Example 1
Style
| style_id | article_type |
|---|---|
| 10 | Jeans |
| 11 | Jeans |
| 12 | Kurta |
| 13 | Sneakers |
| 14 | Kurta |
Wishlist
| user_id | style_id | added_on |
|---|---|---|
| 1 | 10 | 2024-04-01 |
| 2 | 10 | 2024-04-10 |
| 2 | 11 | 2024-04-12 |
| 3 | 12 | 2024-04-02 |
| 1 | 14 | 2024-04-03 |
| 4 | 13 | 2024-04-04 |
OrderLine
| order_line_id | user_id | style_id | ordered_on | status |
|---|---|---|---|---|
| 1 | 1 | 10 | 2024-04-05 | delivered |
| 2 | 1 | 10 | 2024-04-07 | delivered |
| 3 | 2 | 10 | 2024-04-09 | delivered |
| 4 | 2 | 11 | 2024-04-12 | returned |
| 5 | 3 | 12 | 2024-04-06 | cancelled |
| 6 | 1 | 14 | 2024-04-20 | delivered |
| 7 | 4 | 12 | 2024-04-21 | delivered |
Output
| article_type | wishlisted | converted | conversion_pct |
|---|---|---|---|
| Jeans | 3 | 2 | 66.7 |
| Kurta | 2 | 1 | 50 |
| Sneakers | 1 | 0 | 0 |
How to solve Myntra: Wishlist-to-Purchase Conversion by Article Type
The unit being counted is the wishlist entry, and each entry either converted or did not. Joining OrderLine directly onto Wishlist would break that: shopper 1 bought style 10 twice, so the join would produce two rows for one entry and inflate both counts. The solution instead asks a yes/no question per entry with a correlated EXISTS — is there an order line of the same shopper and style, not cancelled, dated on or after the save? — and adds the yeses with SUM(CASE … THEN 1 ELSE 0 END).
The conditions are deliberate: an order placed before the save (shopper 2's style 10) is not a conversion of that save; a returned order still started as a sale and counts; a cancelled one does not. The entries are grouped by the style's article type, COUNT(*) gives the entries, and the percentage is rounded to one place, ordered highest first with ties broken by name.
The alternative builds the set of converted (shopper, style) pairs once, with DISTINCT to collapse repeat purchases, and LEFT JOINs it: COUNT(c.user_id) counts the entries that found a partner. Both are linear in the tables with an index on (user_id, style_id).
Reference solution (MySQL)
SELECT s.article_type,
COUNT(*) AS wishlisted,
SUM(CASE WHEN EXISTS (SELECT 1 FROM OrderLine o
WHERE o.user_id = w.user_id AND o.style_id = w.style_id
AND o.status <> 'cancelled' AND o.ordered_on >= w.added_on)
THEN 1 ELSE 0 END) AS converted,
ROUND(100 * SUM(CASE WHEN EXISTS (SELECT 1 FROM OrderLine o
WHERE o.user_id = w.user_id AND o.style_id = w.style_id
AND o.status <> 'cancelled' AND o.ordered_on >= w.added_on)
THEN 1 ELSE 0 END) / COUNT(*), 1) AS conversion_pct
FROM Wishlist w
JOIN Style s ON s.style_id = w.style_id
GROUP BY s.article_type
ORDER BY conversion_pct DESC, s.article_type ASCAnother way
SELECT s.article_type, COUNT(*) AS wishlisted, COUNT(c.user_id) AS converted,
ROUND(100 * COUNT(c.user_id) / COUNT(*), 1) AS conversion_pct
FROM Wishlist w
JOIN Style s ON s.style_id = w.style_id
LEFT JOIN (
SELECT DISTINCT w2.user_id, w2.style_id
FROM Wishlist w2
JOIN OrderLine o ON o.user_id = w2.user_id AND o.style_id = w2.style_id
AND o.status <> 'cancelled' AND o.ordered_on >= w2.added_on
) c ON c.user_id = w.user_id AND c.style_id = w.style_id
GROUP BY s.article_type
ORDER BY conversion_pct DESC, s.article_type← Myntra: Styles Marked Down by at Least 40% for EORS Day One · Myntra: Delivered GMV by Indian Fiscal Year and Quarter →