Myntra: Return Requests Raised After the Category's Window

Easy
804.

Myntra: Return Requests Raised After the Category's Window

EasyJoinsDates

MySQL

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

ColumnType
category_idint
category_namevarchar
return_window_daysint

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

ColumnType
order_item_idint
category_idint
delivered_ondate

Primary key: order_item_id.

One row per delivered item.

Table: ReturnRequest

ColumnType
return_idint
order_item_idint
requested_ondate

Primary key: return_id.

One row per return request; requested_on is never before the delivery date.

Example

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

Category

category_idcategory_namereturn_window_days
1Western Wear14
2Footwear7
3Innerwear0
4Watches10

OrderItem

order_item_idcategory_iddelivered_on
1112024-03-01
1212024-03-01
1322024-03-05
1432024-03-08
1532024-03-08
1642024-03-10

ReturnRequest

return_idorder_item_idrequested_on
1112024-03-15
2122024-03-16
3132024-03-13
4142024-03-08
5152024-03-09
6162024-03-12

Output

return_idcategory_namedays_after_delivery
2Western Wear15
3Footwear8
5Innerwear1
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: 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

ColumnType
category_idint
category_namevarchar
return_window_daysint

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

ColumnType
order_item_idint
category_idint
delivered_ondate

Primary key: order_item_id.

One row per delivered item.

Table: ReturnRequest

ColumnType
return_idint
order_item_idint
requested_ondate

Primary key: return_id.

One row per return request; requested_on is never before the delivery date.

Examples

Example 1

Category

category_idcategory_namereturn_window_days
1Western Wear14
2Footwear7
3Innerwear0
4Watches10

OrderItem

order_item_idcategory_iddelivered_on
1112024-03-01
1212024-03-01
1322024-03-05
1432024-03-08
1532024-03-08
1642024-03-10

ReturnRequest

return_idorder_item_idrequested_on
1112024-03-15
2122024-03-16
3132024-03-13
4142024-03-08
5152024-03-09
6162024-03-12

Output

return_idcategory_namedays_after_delivery
2Western Wear15
3Footwear8
5Innerwear1

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_days

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