Myntra: Return Requests Raised After the Category's Window
Each category has its own return window, counted in days from delivery; innerwear, for hygiene reasons, may only be returned on the day it arrives.
- Difficulty: Easy
- Topics: Joins, Dates
- Dialect: MySQL
- Problem: #804
Problem statement
Each category has its own return window, counted in days from delivery; innerwear, for hygiene reasons, may only be returned on the day it arrives. The returns-policy team wants to see the requests that came in too late.
A request is late when the number of days from delivered_on to requested_on is greater than the category's return_window_days — a request on the last day of the window is still on time. Return return_id, category_name and days_after_delivery for every late request, 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: Category
| Column | Type |
|---|---|
| category_id | int |
| category_name | varchar |
| return_window_days | int |
Primary key: category_id.
return_window_days is how many days after delivery a return may still be requested (0 = delivery day only).
Table: OrderItem
| Column | Type |
|---|---|
| order_item_id | int |
| category_id | int |
| delivered_on | date |
Primary key: order_item_id.
One row per delivered item.
Table: ReturnRequest
| Column | Type |
|---|---|
| return_id | int |
| order_item_id | int |
| requested_on | date |
Primary key: return_id.
One row per return request; requested_on is never before the delivery date.
Examples
Example 1
Category
| category_id | category_name | return_window_days |
|---|---|---|
| 1 | Western Wear | 14 |
| 2 | Footwear | 7 |
| 3 | Innerwear | 0 |
| 4 | Watches | 10 |
OrderItem
| order_item_id | category_id | delivered_on |
|---|---|---|
| 11 | 1 | 2024-03-01 |
| 12 | 1 | 2024-03-01 |
| 13 | 2 | 2024-03-05 |
| 14 | 3 | 2024-03-08 |
| 15 | 3 | 2024-03-08 |
| 16 | 4 | 2024-03-10 |
ReturnRequest
| return_id | order_item_id | requested_on |
|---|---|---|
| 1 | 11 | 2024-03-15 |
| 2 | 12 | 2024-03-16 |
| 3 | 13 | 2024-03-13 |
| 4 | 14 | 2024-03-08 |
| 5 | 15 | 2024-03-09 |
| 6 | 16 | 2024-03-12 |
Output
| return_id | category_name | days_after_delivery |
|---|---|---|
| 2 | Western Wear | 15 |
| 3 | Footwear | 8 |
| 5 | Innerwear | 1 |
How to solve Myntra: Return Requests Raised After the Category's Window
Three facts decide whether a request is late, and they live in three tables: the request date on ReturnRequest, the delivery date on OrderItem, and the window on Category. Two inner joins along the keys put them on one row; every request names an item and every item a category, so nothing is lost.
DATEDIFF(r.requested_on, i.delivered_on) is the number of days since delivery — 0 for a request on the delivery day itself. The request is late when that number is strictly greater than the window, so a request exactly 14 days after delivery in a 14-day category is on time, and an innerwear request on the delivery day (0 > 0 is false) is on time while one the next day is not.
The same expression is selected as days_after_delivery. The alternative computes it once with TIMESTAMPDIFF(DAY, …) in a derived table and filters on the alias outside, which avoids repeating the expression. Either way it is one pass over the requests with two key lookups each.
Reference solution (MySQL)
SELECT r.return_id, c.category_name, DATEDIFF(r.requested_on, i.delivered_on) AS days_after_delivery
FROM ReturnRequest r
JOIN OrderItem i ON i.order_item_id = r.order_item_id
JOIN Category c ON c.category_id = i.category_id
WHERE DATEDIFF(r.requested_on, i.delivered_on) > c.return_window_daysAnother way
SELECT t.return_id, t.category_name, t.days_after_delivery
FROM (
SELECT r.return_id, c.category_name, c.return_window_days,
TIMESTAMPDIFF(DAY, i.delivered_on, r.requested_on) AS days_after_delivery
FROM ReturnRequest r, OrderItem i, Category c
WHERE i.order_item_id = r.order_item_id AND c.category_id = i.category_id
) t
WHERE t.days_after_delivery > t.return_window_days← Myntra: Insider Points Earned on Each Delivered Order · Myntra: M-Now Deliveries Inside the 30-Minute Promise by Store →