Flipkart: First Day a Seller Logged Three Returns in Seven Days
Seller-quality monitoring raises a flag the first time a seller collects 3 or more returns within 7 days.
- Difficulty: Hard
- Topics: Joins, Subqueries, Dates, Window Functions
- Dialect: MySQL
- Problem: #671
Problem statement
Seller-quality monitoring raises a flag the first time a seller collects 3 or more returns within 7 days. The window for a day D is D and the six days before it (from D - 6 to D, inclusive).
For each seller, find the earliest return date D on which the returns dated within D's seven-day window number at least 3 (several returns can share a date). Return seller_name and flagged_on (that date) for every seller that was ever flagged. Seller names are unique. Order by flagged_on, then seller_name.
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: Seller
| Column | Type |
|---|---|
| seller_id | int |
| seller_name | varchar |
Primary key: seller_id.
One row per seller.
Table: SellerReturn
| Column | Type |
|---|---|
| return_id | int |
| seller_id | int |
| returned_on | date |
Primary key: return_id.
One row per customer return received by a seller; seller_id always names a seller.
Examples
Example 1
Seller
| seller_id | seller_name |
|---|---|
| 1 | Metro Gadgets |
| 2 | Desi Weaves |
| 3 | Pixel Point |
| 4 | Kirana Kart |
SellerReturn
| return_id | seller_id | returned_on |
|---|---|---|
| 1 | 1 | 2024-10-01 |
| 2 | 1 | 2024-10-05 |
| 3 | 1 | 2024-10-08 |
| 4 | 1 | 2024-10-10 |
| 5 | 2 | 2024-10-03 |
| 6 | 2 | 2024-10-03 |
| 7 | 2 | 2024-10-09 |
| 8 | 3 | 2024-10-02 |
| 9 | 3 | 2024-10-09 |
| 10 | 3 | 2024-10-16 |
| 11 | 4 | 2024-10-12 |
| 12 | 4 | 2024-10-12 |
| 13 | 4 | 2024-10-12 |
Output
| seller_name | flagged_on |
|---|---|
| Desi Weaves | 2024-10-09 |
| Metro Gadgets | 2024-10-10 |
| Kirana Kart | 2024-10-12 |
How to solve Flipkart: First Day a Seller Logged Three Returns in Seven Days
The rule is a trailing window by date, not by row count: the window ending on day D holds every return from D − 6 to D. For each return, a correlated subquery counts the seller's returns in the window that ends on that return's date, using BETWEEN DATE_SUB(returned_on, INTERVAL 6 DAY) AND returned_on. Dates that reach three are hits; the seller's flag date is the earliest hit, MIN(returned_on). Only dates that actually have a return are candidates, which is enough — the count can only rise on a day a return arrives.
Same-day returns all count: three returns on one date flag that date. The window is inclusive at both ends, so returns on the 3rd and the 9th share a window.
The LAG alternative is neater: sort a seller's returns by date (and id), and look two rows back. If that return is within six days of this one, the three rows from it to here fit in one window ending today; and the earliest such row is exactly the earliest flag date. The self-join version pairs each return with every return of the same seller up to six days earlier and keeps those with at least three partners (itself included). The window version is one sort per seller; the others are quadratic per seller.
Reference solution (MySQL)
WITH hit AS (
SELECT r.seller_id, r.returned_on
FROM SellerReturn r
WHERE (SELECT COUNT(*) FROM SellerReturn x
WHERE x.seller_id = r.seller_id
AND x.returned_on BETWEEN DATE_SUB(r.returned_on, INTERVAL 6 DAY) AND r.returned_on) >= 3
)
SELECT s.seller_name, MIN(h.returned_on) AS flagged_on
FROM hit h
JOIN Seller s ON s.seller_id = h.seller_id
GROUP BY s.seller_id, s.seller_name
ORDER BY flagged_on, s.seller_nameAnother way
WITH x AS (
SELECT seller_id, returned_on,
LAG(returned_on, 2) OVER (PARTITION BY seller_id ORDER BY returned_on, return_id) AS two_back
FROM SellerReturn
)
SELECT s.seller_name, MIN(x.returned_on) AS flagged_on
FROM x JOIN Seller s ON s.seller_id = x.seller_id
WHERE x.two_back IS NOT NULL AND DATEDIFF(x.returned_on, x.two_back) <= 6
GROUP BY s.seller_id, s.seller_name
ORDER BY flagged_on, s.seller_nameAnother way
SELECT s.seller_name, MIN(d.returned_on) AS flagged_on
FROM (SELECT a.seller_id, a.returned_on
FROM SellerReturn a
JOIN SellerReturn b ON b.seller_id = a.seller_id AND DATEDIFF(a.returned_on, b.returned_on) BETWEEN 0 AND 6
GROUP BY a.return_id, a.seller_id, a.returned_on
HAVING COUNT(*) >= 3) d
JOIN Seller s ON s.seller_id = d.seller_id
GROUP BY s.seller_id, s.seller_name
ORDER BY flagged_on, s.seller_name← Flipkart: Unscanned Ekart AWB Numbers as Ranges per Hub · Flipkart: How Often Each Product's Buy Box Changed Hands →