Myntra: Jeans Buyers Whose Waist Size Changed Between Orders

Medium
812.

Myntra: Jeans Buyers Whose Waist Size Changed Between Orders

MediumWindow Functions

MySQL

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

ColumnType
order_item_idint
user_idint
article_typevarchar
sizevarchar
ordered_ondate

Primary key: order_item_id.

One row per item ordered. For jeans, size is the waist in inches, such as 32.

Example

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

OrderItem

order_item_iduser_idarticle_typesizeordered_on
11Jeans322024-01-10
21T-shirtL2024-02-01
31Jeans322024-03-05
41Jeans342024-06-20
52Jeans302024-02-14
62Jeans282024-02-14
72T-shirtM2024-03-01
82Jeans302024-04-01
93Jeans362024-05-05

Output

user_idorder_item_idprevious_sizenew_size
143234
263028
282830
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: 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

ColumnType
order_item_idint
user_idint
article_typevarchar
sizevarchar
ordered_ondate

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_iduser_idarticle_typesizeordered_on
11Jeans322024-01-10
21T-shirtL2024-02-01
31Jeans322024-03-05
41Jeans342024-06-20
52Jeans302024-02-14
62Jeans282024-02-14
72T-shirtM2024-03-01
82Jeans302024-04-01
93Jeans362024-05-05

Output

user_idorder_item_idprevious_sizenew_size
143234
263028
282830

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

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