Myntra: Each Brand's Three Most-Wishlisted Styles in One Row

Hard
819.

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

ColumnType
style_idint
brandvarchar
style_namevarchar

Primary key: style_id.

One row per style; names may repeat across brands.

Table: WishlistEvent

ColumnTypeValues
user_idint
style_idint
event_atdatetime
actionenumadd, remove

Primary key (together): 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.

Example

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

Style

style_idbrandstyle_name
1RoadsterSlim Fit Jeans
2RoadsterOversized Shirt
3RoadsterCargo Joggers
4RoadsterRunning T-shirt
5AnoukPrinted Kurta
6AnoukAnarkali Kurta
7HRXChunky Sneakers

WishlistEvent

user_idstyle_idevent_ataction
112024-11-01 10:00:00add
212024-11-01 11:00:00add
312024-11-02 09:00:00add
312024-11-03 09:00:00remove
122024-11-01 10:05:00add
222024-11-02 12:00:00add
132024-11-01 10:10:00add
232024-11-02 13:00:00add
342024-11-02 10:00:00add
152024-11-04 08:00:00remove
4 more rows

Output

brandtop_1top_2top_3
AnoukAnarkali KurtaNULLNULL
RoadsterSlim Fit JeansOversized ShirtCargo Joggers
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: 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

ColumnType
style_idint
brandvarchar
style_namevarchar

Primary key: style_id.

One row per style; names may repeat across brands.

Table: WishlistEvent

ColumnType
user_idint
style_idint
event_atdatetime
actionenum(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_idbrandstyle_name
1RoadsterSlim Fit Jeans
2RoadsterOversized Shirt
3RoadsterCargo Joggers
4RoadsterRunning T-shirt
5AnoukPrinted Kurta
6AnoukAnarkali Kurta
7HRXChunky Sneakers

WishlistEvent

user_idstyle_idevent_ataction
112024-11-01 10:00:00add
212024-11-01 11:00:00add
312024-11-02 09:00:00add
312024-11-03 09:00:00remove
122024-11-01 10:05:00add
222024-11-02 12:00:00add
132024-11-01 10:10:00add
232024-11-02 13:00:00add
342024-11-02 10:00:00add
152024-11-04 08:00:00remove
152024-11-01 08:00:00add
262024-11-05 19:00:00add
372024-11-01 09:00:00add
372024-11-02 09:00:00remove

Output

brandtop_1top_2top_3
AnoukAnarkali KurtaNULLNULL
RoadsterSlim Fit JeansOversized ShirtCargo 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 brand

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