Flipkart: Refund SLA Breaches by Product Category
Flipkart promises a refund within 5 days of the return being picked up. The returns team audits the promise on 2024-11-30.
- Difficulty: Medium
- Topics: Aggregation, Dates, Conditional Logic
- Dialect: MySQL
- Problem: #656
Problem statement
Flipkart promises a refund within 5 days of the return being picked up. The returns team audits the promise on 2024-11-30.
Only returns that have been picked up (picked_up_on not NULL) are audited. A picked-up return is a breach if it was refunded more than 5 days after pickup, or if it is still not refunded and the audit date is more than 5 days after pickup. A refund exactly 5 days after pickup is on time. For each category with at least one picked-up return, return category, picked_up (its picked-up returns) and breaches. Order by breaches descending, then category.
A practice scenario set at Flipkart: the tables and data are invented, and this is not a question from, or endorsed by, Flipkart.
Tables
Table: ReturnRequest
| Column | Type |
|---|---|
| return_id | int |
| category | varchar |
| picked_up_on | date |
| refunded_on | date |
Primary key: return_id.
One row per return. picked_up_on is NULL while the courier has not collected the item; refunded_on is NULL until the refund is credited (never before pickup, never after the audit date).
Examples
Example 1
ReturnRequest
| return_id | category | picked_up_on | refunded_on |
|---|---|---|---|
| 1 | Mobiles | 2024-11-10 | 2024-11-15 |
| 2 | Mobiles | 2024-11-10 | 2024-11-16 |
| 3 | Fashion | 2024-11-20 | NULL |
| 4 | Fashion | 2024-11-25 | NULL |
| 5 | Home | NULL | NULL |
| 6 | Fashion | 2024-11-01 | 2024-11-03 |
| 7 | Books | 2024-11-24 | NULL |
Output
| category | picked_up | breaches |
|---|---|---|
| Books | 1 | 1 |
| Fashion | 3 | 1 |
| Mobiles | 2 | 1 |
How to solve Flipkart: Refund SLA Breaches by Product Category
Two kinds of breach share one rule once you notice that a refund still pending on audit day is late exactly when it would be late if it were credited on audit day. COALESCE(refunded_on, '2024-11-30') makes that substitution, and DATEDIFF(that, picked_up_on) > 5 is then the breach test for every audited row. Five days exactly is on time, so the comparison is strict.
Returns that were never picked up are removed in WHERE, so they count neither as audited nor as breaches, and a category made only of them never forms a group. SUM(CASE … THEN 1 ELSE 0 END) counts breaches beside COUNT(*) in the same pass — conditional aggregation.
The alternative spells the two cases out, uses COUNT(CASE …) (a CASE without ELSE is NULL, which COUNT skips), compares against DATE_ADD(picked_up_on, INTERVAL 5 DAY), and drops never-picked categories with HAVING instead of WHERE. Either is one scan of the returns.
Reference solution (MySQL)
SELECT category,
COUNT(*) AS picked_up,
SUM(CASE WHEN DATEDIFF(COALESCE(refunded_on, '2024-11-30'), picked_up_on) > 5 THEN 1 ELSE 0 END) AS breaches
FROM ReturnRequest
WHERE picked_up_on IS NOT NULL
GROUP BY category
ORDER BY breaches DESC, categoryAnother way
SELECT category, COUNT(picked_up_on) AS picked_up,
COUNT(CASE WHEN refunded_on IS NULL AND DATEDIFF('2024-11-30', picked_up_on) > 5 THEN 1
WHEN refunded_on IS NOT NULL AND refunded_on > DATE_ADD(picked_up_on, INTERVAL 5 DAY) THEN 1 END) AS breaches
FROM ReturnRequest
GROUP BY category
HAVING COUNT(picked_up_on) > 0
ORDER BY breaches DESC, category← Flipkart: Instant Discount From Each Bank Offer · Flipkart: Months Members Burned More SuperCoins Than Earned →