Myntra: Jeans Buyers Whose Waist Size Changed Between Orders
The size-recommendation model pre-selects a shopper's usual jeans size.
- Difficulty: Medium
- Topics: Window Functions
- Dialect: MySQL
- Problem: #812
Problem statement
The size-recommendation model pre-selects a shopper's usual jeans size. To measure how often that guess would be stale, the data-science team wants every point where a shopper's waist size changed from one jeans purchase to their next.
Look only at items with article_type = 'Jeans'. Take each shopper's jeans items in order of ordered_on, then order_item_id for items ordered the same day. Return user_id, order_item_id, previous_size (the size of the shopper's previous jeans item) and new_size (this item's size) for every item whose size differs from the previous one. A shopper's first jeans item is never returned. 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: OrderItem
| Column | Type |
|---|---|
| order_item_id | int |
| user_id | int |
| article_type | varchar |
| size | varchar |
| ordered_on | date |
Primary key: order_item_id.
One row per item ordered. For jeans, size is the waist in inches, such as 32.
Examples
Example 1
OrderItem
| order_item_id | user_id | article_type | size | ordered_on |
|---|---|---|---|---|
| 1 | 1 | Jeans | 32 | 2024-01-10 |
| 2 | 1 | T-shirt | L | 2024-02-01 |
| 3 | 1 | Jeans | 32 | 2024-03-05 |
| 4 | 1 | Jeans | 34 | 2024-06-20 |
| 5 | 2 | Jeans | 30 | 2024-02-14 |
| 6 | 2 | Jeans | 28 | 2024-02-14 |
| 7 | 2 | T-shirt | M | 2024-03-01 |
| 8 | 2 | Jeans | 30 | 2024-04-01 |
| 9 | 3 | Jeans | 36 | 2024-05-05 |
Output
| user_id | order_item_id | previous_size | new_size |
|---|---|---|---|
| 1 | 4 | 32 | 34 |
| 2 | 6 | 30 | 28 |
| 2 | 8 | 28 | 30 |
How to solve Myntra: Jeans Buyers Whose Waist Size Changed Between Orders
"The previous jeans item" is a question about row order within a shopper, which is what LAG answers. Filtering to jeans first matters: shopper 1 bought a T-shirt in size L between two pairs of 32-inch jeans, and if that row stayed in, the second pair would wrongly look like a change from L to 32.
LAG(size) OVER (PARTITION BY user_id ORDER BY ordered_on, order_item_id) gives every jeans item the size of the same shopper's previous jeans item. The order_item_id tie-breaker fixes the order of two items bought on the same day — shopper 2 bought a 30 and a 28 together, and the rule in the statement makes that a change from 30 to 28. Then the outer query keeps the rows whose sizes differ. A shopper's first item has a NULL previous size; previous_size <> size is NULL there and filters it out anyway, but stating IS NOT NULL makes the intent clear.
The alternative finds the previous item with a correlated subquery that sorts the shopper's earlier jeans newest first and takes one. It reads the same, but runs a subquery per item; the window version sorts once.
Reference solution (MySQL)
WITH jeans AS (
SELECT user_id, order_item_id, size,
LAG(size) OVER (PARTITION BY user_id ORDER BY ordered_on, order_item_id) AS previous_size
FROM OrderItem
WHERE article_type = 'Jeans'
)
SELECT user_id, order_item_id, previous_size, size AS new_size
FROM jeans
WHERE previous_size IS NOT NULL AND previous_size <> sizeAnother way
SELECT user_id, order_item_id, previous_size, new_size
FROM (
SELECT o.user_id, o.order_item_id, o.size AS new_size,
(SELECT p.size FROM OrderItem p
WHERE p.user_id = o.user_id AND p.article_type = 'Jeans'
AND (p.ordered_on < o.ordered_on OR (p.ordered_on = o.ordered_on AND p.order_item_id < o.order_item_id))
ORDER BY p.ordered_on DESC, p.order_item_id DESC
LIMIT 1) AS previous_size
FROM OrderItem o
WHERE o.article_type = 'Jeans'
) t
WHERE previous_size <> new_size← Myntra: Delivered GMV by Indian Fiscal Year and Quarter · Myntra: Size Exchanges Blocked by an Unavailable Size, per Brand →