Myntra: Shoppers Who Reordered the Size They Returned as Too Small

Medium
807.

Myntra: Shoppers Who Reordered the Size They Returned as Too Small

MediumJoins

MySQL

The fit-recommendation team wants to know whether its "you returned this size as too small" nudge would have helped: who sent back a size as too small and then bought the same size again of the same brand and article type?

Find every item returned with reason = 'size_too_small' for which the same shopper later ordered an item of the same brand, article_type and size, with an ordered_on strictly after the returned item's ordered_on. Return the distinct combinations user_id, brand, article_type, size, 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: OrderItem

ColumnType
order_item_idint
user_idint
brandvarchar
article_typevarchar
sizevarchar
ordered_ondate

Primary key: order_item_id.

One row per item ordered.

Table: ReturnRequest

ColumnType
return_idint
order_item_idint
reasonvarchar

Primary key: return_id.

One row per returned item, with the reason the shopper gave.

Example

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

OrderItem

order_item_iduser_idbrandarticle_typesizeordered_on
11RoadsterJeans322024-02-01
21RoadsterJeans322024-03-10
31RoadsterJeans322024-04-02
42HRXT-shirtM2024-02-05
52HRXT-shirtL2024-02-20
63AnoukKurtaS2024-03-01
73AnoukKurtaS2024-03-15
84RoadsterJeans302024-05-05
94RoadsterJeans302024-05-05
102RoadsterT-shirtM2024-03-01

ReturnRequest

return_idorder_item_idreason
11size_too_small
24size_too_small
36size_too_large
48size_too_small

Output

user_idbrandarticle_typesize
1RoadsterJeans32
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: Shoppers Who Reordered the Size They Returned as Too Small

The fit-recommendation team wants to know whether its "you returned this size as too small" nudge would have helped: who sent back a size as too small and…

  • Difficulty: Medium
  • Topics: Joins
  • Dialect: MySQL
  • Problem: #807

Problem statement

The fit-recommendation team wants to know whether its "you returned this size as too small" nudge would have helped: who sent back a size as too small and then bought the same size again of the same brand and article type?

Find every item returned with reason = 'size_too_small' for which the same shopper later ordered an item of the same brand, article_type and size, with an ordered_on strictly after the returned item's ordered_on. Return the distinct combinations user_id, brand, article_type, size, 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: OrderItem

ColumnType
order_item_idint
user_idint
brandvarchar
article_typevarchar
sizevarchar
ordered_ondate

Primary key: order_item_id.

One row per item ordered.

Table: ReturnRequest

ColumnType
return_idint
order_item_idint
reasonvarchar

Primary key: return_id.

One row per returned item, with the reason the shopper gave.

Examples

Example 1

OrderItem

order_item_iduser_idbrandarticle_typesizeordered_on
11RoadsterJeans322024-02-01
21RoadsterJeans322024-03-10
31RoadsterJeans322024-04-02
42HRXT-shirtM2024-02-05
52HRXT-shirtL2024-02-20
63AnoukKurtaS2024-03-01
73AnoukKurtaS2024-03-15
84RoadsterJeans302024-05-05
94RoadsterJeans302024-05-05
102RoadsterT-shirtM2024-03-01

ReturnRequest

return_idorder_item_idreason
11size_too_small
24size_too_small
36size_too_large
48size_too_small

Output

user_idbrandarticle_typesize
1RoadsterJeans32

How to solve Myntra: Shoppers Who Reordered the Size They Returned as Too Small

Two items of the same shopper have to be compared, so OrderItem appears twice. Alias a is the returned item: joining it to ReturnRequest with reason = 'size_too_small' keeps only items sent back as too small. Alias b is the reorder: a self join on the same user_id, brand, article_type and size, with b.ordered_on > a.ordered_on.

Each condition matters. Same-day items are not "later", so shopper 4's two identical jeans on one day do not count. A different size (shopper 2 going from M to L) is the nudge working, not failing. A return for being too large is a different story. And because one returned item can pair with several later orders, or one shopper can have several qualifying pairs, the raw join repeats combinations — DISTINCT collapses them to one row each.

The alternative filters the returned items with IN, tests for a later reorder with a correlated EXISTS, and uses GROUP BY to make the rows distinct. With an index on (user_id, brand, article_type, size) both find each item's partners directly.

Reference solution (MySQL)

SELECT DISTINCT a.user_id, a.brand, a.article_type, a.size
FROM OrderItem a
JOIN ReturnRequest r ON r.order_item_id = a.order_item_id AND r.reason = 'size_too_small'
JOIN OrderItem b ON b.user_id = a.user_id
               AND b.brand = a.brand
               AND b.article_type = a.article_type
               AND b.size = a.size
               AND b.ordered_on > a.ordered_on

Another way

SELECT a.user_id, a.brand, a.article_type, a.size
FROM OrderItem a
WHERE a.order_item_id IN (SELECT order_item_id FROM ReturnRequest WHERE reason = 'size_too_small')
  AND EXISTS (SELECT 1 FROM OrderItem b
              WHERE b.user_id = a.user_id AND b.brand = a.brand AND b.article_type = a.article_type
                AND b.size = a.size AND b.ordered_on > a.ordered_on)
GROUP BY a.user_id, a.brand, a.article_type, a.size

← Myntra: Size-Related Return Rate by Article Type and Size · Myntra: Creator Store Commission for October 2024 →