Myntra: Most Common Return Reasons in 2024 — SQL Problem
The returns team is writing its annual review and wants to know which reasons shoppers picked most often when they sent something back or asked for an…
- Difficulty: Easy
- Topics: Aggregation, Dates
- Dialect: MySQL
- Problem: #800
Problem statement
The returns team is writing its annual review and wants to know which reasons shoppers picked most often when they sent something back or asked for an exchange.
Count the requests raised in calendar year 2024 (requested_on from 2024-01-01 to 2024-12-31), returns and exchanges together, per reason. Return reason and requests, ordered by requests descending, then by reason alphabetically. Reasons with no 2024 request do not appear.
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: ReturnRequest
| Column | Type |
|---|---|
| return_id | int |
| order_item_id | int |
| reason | varchar |
| request_type | enum(return, exchange) |
| requested_on | date |
Primary key: return_id.
One row per return or exchange request. reason is the code the shopper picked, such as size_too_small or damaged.
Examples
Example 1
ReturnRequest
| return_id | order_item_id | reason | request_type | requested_on |
|---|---|---|---|---|
| 1 | 501 | size_too_small | exchange | 2024-01-04 |
| 2 | 502 | size_too_large | return | 2024-02-11 |
| 3 | 503 | size_too_small | return | 2024-03-20 |
| 4 | 504 | damaged | return | 2023-12-30 |
| 5 | 505 | size_too_large | exchange | 2024-06-01 |
| 6 | 506 | colour_different | return | 2024-12-31 |
| 7 | 507 | changed_mind | return | 2025-01-01 |
| 8 | 508 | damaged | return | 2024-07-15 |
Output
| reason | requests |
|---|---|
| size_too_large | 2 |
| size_too_small | 2 |
| colour_different | 1 |
| damaged | 1 |
How to solve Myntra: Most Common Return Reasons in 2024
The question is a filter, a group and a sort. The WHERE clause keeps only requests raised in 2024; written as requested_on >= '2024-01-01' AND requested_on < '2025-01-01' it includes both New Year's Eve and New Year's Day of 2024 and leaves out the two neighbouring days, which the data puts there on purpose. YEAR(requested_on) = 2024 says the same thing and is the alternative, though it cannot use an index on the date.
GROUP BY reason then makes one group per reason that still has rows, and COUNT(*) counts them. A reason with no 2024 request has no rows left after the filter, so it forms no group and does not appear — exactly what the statement asks.
The order is fixed: most requests first, and when two reasons tie, alphabetical by reason. Without the second key the tied rows could come back in either order. The query reads the table once and sorts a handful of groups.
Reference solution (MySQL)
SELECT reason, COUNT(*) AS requests
FROM ReturnRequest
WHERE requested_on >= '2024-01-01' AND requested_on < '2025-01-01'
GROUP BY reason
ORDER BY requests DESC, reason ASCAnother way
SELECT reason, COUNT(return_id) AS requests FROM ReturnRequest WHERE YEAR(requested_on) = 2024 GROUP BY reason ORDER BY COUNT(return_id) DESC, reason← Myntra: Live Styles With No Approved Catalogue Image · Myntra: Product Display Names From Brand, Colour and Article →