Myntra: Wishlist-to-Purchase Conversion by Article Type

Medium
810.

Myntra: Wishlist-to-Purchase Conversion by Article Type

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

ColumnType
style_idint
article_typevarchar

Primary key: style_id.

One row per style.

Table: Wishlist

ColumnType
user_idint
style_idint
added_ondate

Primary key (together): user_id, style_id.

One row per style a shopper saved.

Table: OrderLine

ColumnTypeValues
order_line_idint
user_idint
style_idint
ordered_ondate
statusenumdelivered, returned, cancelled

Primary key: order_line_id.

One row per item bought.

Example

The rows must come back in the order shown.

Style

style_idarticle_type
10Jeans
11Jeans
12Kurta
13Sneakers
14Kurta

Wishlist

user_idstyle_idadded_on
1102024-04-01
2102024-04-10
2112024-04-12
3122024-04-02
1142024-04-03
4132024-04-04

OrderLine

order_line_iduser_idstyle_idordered_onstatus
11102024-04-05delivered
21102024-04-07delivered
32102024-04-09delivered
42112024-04-12returned
53122024-04-06cancelled
61142024-04-20delivered
74122024-04-21delivered

Output

article_typewishlistedconvertedconversion_pct
Jeans3266.7
Kurta2150
Sneakers100
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: 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

ColumnType
style_idint
article_typevarchar

Primary key: style_id.

One row per style.

Table: Wishlist

ColumnType
user_idint
style_idint
added_ondate

Primary key: user_id, style_id.

One row per style a shopper saved.

Table: OrderLine

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

Primary key: order_line_id.

One row per item bought.

Examples

Example 1

Style

style_idarticle_type
10Jeans
11Jeans
12Kurta
13Sneakers
14Kurta

Wishlist

user_idstyle_idadded_on
1102024-04-01
2102024-04-10
2112024-04-12
3122024-04-02
1142024-04-03
4132024-04-04

OrderLine

order_line_iduser_idstyle_idordered_onstatus
11102024-04-05delivered
21102024-04-07delivered
32102024-04-09delivered
42112024-04-12returned
53122024-04-06cancelled
61142024-04-20delivered
74122024-04-21delivered

Output

article_typewishlistedconvertedconversion_pct
Jeans3266.7
Kurta2150
Sneakers100

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 ASC

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