Myntra: Serial Returners by Their Last Five Delivered Orders

Hard
822.

Myntra: Serial Returners by Their Last Five Delivered Orders

To decide who loses free returns, the returns-abuse team looks at each shopper's recent behaviour only, not their whole history.

Ignore cancelled orders. A shopper's last five orders are their five with the latest delivered_on, breaking ties by the higher order_id. Return user_id, name and returns_in_last_five for every shopper who has at least five non-cancelled orders and whose last five include at least four with status = 'returned' (exchanges are not returns). 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: Shopper

ColumnType
user_idint
namevarchar

Primary key: user_id.

One row per account.

Table: ShopOrder

ColumnTypeValues
order_idint
user_idint
delivered_ondate
statusenumkept, returned, exchanged, cancelled

Primary key: order_id.

One row per order. delivered_on is NULL exactly when the order was cancelled.

Example

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

Shopper

user_idname
1Aarav
2Diya
3Kabir
4Meera

ShopOrder

order_iduser_iddelivered_onstatus
112024-01-05kept
212024-02-05returned
312024-03-05returned
412024-04-05kept
512024-05-05returned
612024-06-05returned
722024-01-10returned
822024-02-10returned
922024-03-10exchanged
1022024-04-10returned
12 more rows

Output

user_idnamereturns_in_last_five
1Aarav4
4Meera4
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: Serial Returners by Their Last Five Delivered Orders

To decide who loses free returns, the returns-abuse team looks at each shopper's recent behaviour only, not their whole history. Ignore cancelled orders.

  • Difficulty: Hard
  • Topics: Window Functions, Joins, Aggregation
  • Dialect: MySQL
  • Problem: #822

Problem statement

To decide who loses free returns, the returns-abuse team looks at each shopper's recent behaviour only, not their whole history.

Ignore cancelled orders. A shopper's last five orders are their five with the latest delivered_on, breaking ties by the higher order_id. Return user_id, name and returns_in_last_five for every shopper who has at least five non-cancelled orders and whose last five include at least four with status = 'returned' (exchanges are not returns). 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: Shopper

ColumnType
user_idint
namevarchar

Primary key: user_id.

One row per account.

Table: ShopOrder

ColumnType
order_idint
user_idint
delivered_ondate
statusenum(kept, returned, exchanged, cancelled)

Primary key: order_id.

One row per order. delivered_on is NULL exactly when the order was cancelled.

Examples

Example 1

Shopper

user_idname
1Aarav
2Diya
3Kabir
4Meera

ShopOrder

order_iduser_iddelivered_onstatus
112024-01-05kept
212024-02-05returned
312024-03-05returned
412024-04-05kept
512024-05-05returned
612024-06-05returned
722024-01-10returned
822024-02-10returned
922024-03-10exchanged
1022024-04-10returned
1122024-05-10returned
1222024-05-10kept
1332024-03-01returned
1432024-03-02returned
1532024-03-03returned
1632024-03-04returned
1742024-02-01returned
1842024-02-02returned
1942024-02-03kept
2042024-02-04returned
2142024-02-05returned
224NULLcancelled

Output

user_idnamereturns_in_last_five
1Aarav4
4Meera4

How to solve Myntra: Serial Returners by Their Last Five Delivered Orders

This is a top-N per group problem followed by a condition on the group. Cancelled orders are removed first: they have no delivery date and were never in the shopper's hands. Then ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY delivered_on DESC, order_id DESC) numbers each shopper's orders from the most recent, and positions 1 to 5 are the last five. The order_id tie-break matters when two orders arrived the same day at the edge of the window.

Grouping those rows per shopper gives two numbers: how many orders were seen (fewer than five means the shopper does not have five orders yet, like Kabir) and how many of them were returned. Only returned counts — Diya's exchange is not a return, and her older return outside the window must not count either; looking at her whole history would wrongly flag her. Meera's cancelled order is ignored rather than pushing an older order out.

The alternative selects the last five without a window: an order is among them when fewer than five of the shopper's non-cancelled orders are newer in (date, id) order, counted by a correlated subquery, and HAVING applies both conditions. It is quadratic per shopper; the window version is one sort.

Reference solution (MySQL)

WITH ranked AS (
  SELECT user_id, status,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY delivered_on DESC, order_id DESC) AS recency
  FROM ShopOrder
  WHERE status <> 'cancelled'
), last_five AS (
  SELECT user_id,
         COUNT(*) AS orders_seen,
         SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returns_in_last_five
  FROM ranked
  WHERE recency <= 5
  GROUP BY user_id
)
SELECT s.user_id, s.name, l.returns_in_last_five
FROM last_five l
JOIN Shopper s ON s.user_id = l.user_id
WHERE l.orders_seen = 5 AND l.returns_in_last_five >= 4

Another way

SELECT s.user_id, s.name, SUM(CASE WHEN o.status = 'returned' THEN 1 ELSE 0 END) AS returns_in_last_five
FROM Shopper s
JOIN ShopOrder o ON o.user_id = s.user_id
WHERE o.status <> 'cancelled'
  AND (SELECT COUNT(*) FROM ShopOrder p
       WHERE p.user_id = o.user_id AND p.status <> 'cancelled'
         AND (p.delivered_on > o.delivered_on OR (p.delivered_on = o.delivered_on AND p.order_id > o.order_id))) < 5
GROUP BY s.user_id, s.name
HAVING COUNT(*) = 5 AND SUM(CASE WHEN o.status = 'returned' THEN 1 ELSE 0 END) >= 4

← Myntra: The Few Brands Making Up 80% of Each Category's Sale Revenue · Ola: Scooters Still Below MoveOS 4.2 →