Myntra: Each Brand's Three Most-Wishlisted Styles in One Row
Before the sale, each brand's account manager gets a one-line card naming the brand's three styles sitting in the most wishlists right now, so the brand can…
- Difficulty: Hard
- Topics: Window Functions, Subqueries, Conditional Logic
- Dialect: MySQL
- Problem: #819
Problem statement
Before the sale, each brand's account manager gets a one-line card naming the brand's three styles sitting in the most wishlists right now, so the brand can stock up on them.
The wishlist log records every 'add' and 'remove'. A style is in a shopper's wishlist when that shopper's latest event for the style is an 'add'. For each style, count the shoppers who have it in their wishlist; styles with a count of 0 are ignored. For every brand with at least one such style, return one row: brand, top_1, top_2 and top_3 — the style_names of its most-wishlisted, second and third styles, ranked by that count descending with ties broken by the lower style_id. When a brand has fewer than three, the missing columns are NULL. 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: Style
| Column | Type |
|---|---|
| style_id | int |
| brand | varchar |
| style_name | varchar |
Primary key: style_id.
One row per style; names may repeat across brands.
Table: WishlistEvent
| Column | Type |
|---|---|
| user_id | int |
| style_id | int |
| event_at | datetime |
| action | enum(add, remove) |
Primary key: user_id, style_id, event_at.
One row per tap on the heart icon. A shopper never has two events for one style at the same moment.
Examples
Example 1
Style
| style_id | brand | style_name |
|---|---|---|
| 1 | Roadster | Slim Fit Jeans |
| 2 | Roadster | Oversized Shirt |
| 3 | Roadster | Cargo Joggers |
| 4 | Roadster | Running T-shirt |
| 5 | Anouk | Printed Kurta |
| 6 | Anouk | Anarkali Kurta |
| 7 | HRX | Chunky Sneakers |
WishlistEvent
| user_id | style_id | event_at | action |
|---|---|---|---|
| 1 | 1 | 2024-11-01 10:00:00 | add |
| 2 | 1 | 2024-11-01 11:00:00 | add |
| 3 | 1 | 2024-11-02 09:00:00 | add |
| 3 | 1 | 2024-11-03 09:00:00 | remove |
| 1 | 2 | 2024-11-01 10:05:00 | add |
| 2 | 2 | 2024-11-02 12:00:00 | add |
| 1 | 3 | 2024-11-01 10:10:00 | add |
| 2 | 3 | 2024-11-02 13:00:00 | add |
| 3 | 4 | 2024-11-02 10:00:00 | add |
| 1 | 5 | 2024-11-04 08:00:00 | remove |
| 1 | 5 | 2024-11-01 08:00:00 | add |
| 2 | 6 | 2024-11-05 19:00:00 | add |
| 3 | 7 | 2024-11-01 09:00:00 | add |
| 3 | 7 | 2024-11-02 09:00:00 | remove |
Output
| brand | top_1 | top_2 | top_3 |
|---|---|---|---|
| Anouk | Anarkali Kurta | NULL | NULL |
| Roadster | Slim Fit Jeans | Oversized Shirt | Cargo Joggers |
How to solve Myntra: Each Brand's Three Most-Wishlisted Styles in One Row
There are three steps: the current state of every wishlist, a ranking per brand, and a pivot of the top three ranks into columns.
The log has history, so a style is in a wishlist only if the shopper's latest event for it is an add. Keeping the row whose event_at equals the pair's maximum does that; shopper 3 added and then removed Slim Fit Jeans, and shopper 1's Printed Kurta removal is the later row even though it is listed first, so neither counts. HRX's only style was removed, so HRX has no style above 0 and no row.
Counting shoppers per style gives Roadster 2 for Slim Fit Jeans, 2 for Oversized Shirt, 2 for Cargo Joggers and 1 for Running T-shirt. ROW_NUMBER() per brand ordered by count descending and style_id makes the three-way tie resolve to ids 1, 2, 3, and the T-shirt is fourth and dropped. The pivot is GROUP BY brand with MAX(CASE WHEN pos = k THEN style_name END) for k = 1, 2, 3: each position appears at most once per brand, so MAX simply picks it, and a missing position gives NULL — Anouk has only Anarkali Kurta, so its top_2 and top_3 are NULL.
The alternative finds the latest event with ROW_NUMBER() instead of a correlated MAX, computes positions by counting the brand's styles that rank at or ahead of each one, and pivots by joining the ranked rows three times on the position with LEFT JOINs. The conditional aggregate is one grouping; the join version touches the ranked set once per column.
Reference solution (MySQL)
WITH latest AS (
SELECT w.user_id, w.style_id, w.action
FROM WishlistEvent w
WHERE w.event_at = (SELECT MAX(w2.event_at) FROM WishlistEvent w2
WHERE w2.user_id = w.user_id AND w2.style_id = w.style_id)
), counts AS (
SELECT s.brand, s.style_id, s.style_name, COUNT(*) AS shoppers
FROM latest l
JOIN Style s ON s.style_id = l.style_id
WHERE l.action = 'add'
GROUP BY s.brand, s.style_id, s.style_name
), ranked AS (
SELECT brand, style_name,
ROW_NUMBER() OVER (PARTITION BY brand ORDER BY shoppers DESC, style_id) AS pos
FROM counts
)
SELECT brand,
MAX(CASE WHEN pos = 1 THEN style_name END) AS top_1,
MAX(CASE WHEN pos = 2 THEN style_name END) AS top_2,
MAX(CASE WHEN pos = 3 THEN style_name END) AS top_3
FROM ranked
GROUP BY brandAnother way
WITH ordered AS (
SELECT user_id, style_id, action,
ROW_NUMBER() OVER (PARTITION BY user_id, style_id ORDER BY event_at DESC) AS rn
FROM WishlistEvent
), counts AS (
SELECT s.brand, s.style_id, s.style_name, COUNT(*) AS shoppers
FROM ordered o JOIN Style s ON s.style_id = o.style_id
WHERE o.rn = 1 AND o.action = 'add'
GROUP BY s.brand, s.style_id, s.style_name
), ranked AS (
SELECT c.brand, c.style_name,
(SELECT COUNT(*) FROM counts c2
WHERE c2.brand = c.brand
AND (c2.shoppers > c.shoppers OR (c2.shoppers = c.shoppers AND c2.style_id <= c.style_id))) AS pos
FROM counts c
)
SELECT b.brand, r1.style_name AS top_1, r2.style_name AS top_2, r3.style_name AS top_3
FROM (SELECT DISTINCT brand FROM ranked) b
LEFT JOIN ranked r1 ON r1.brand = b.brand AND r1.pos = 1
LEFT JOIN ranked r2 ON r2.brand = b.brand AND r2.pos = 2
LEFT JOIN ranked r3 ON r3.brand = b.brand AND r3.pos = 3← Myntra: EORS First-Time Buyers Who Ordered Again Within 90 Days · Myntra: Sizes Over-Represented in Size Returns per Article Type →