Flipkart: First Day a Seller Logged Three Returns in Seven Days

Hard
671.

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. 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

ColumnType
seller_idint
seller_namevarchar

Primary key: seller_id.

One row per seller.

Table: SellerReturn

ColumnType
return_idint
seller_idint
returned_ondate

Primary key: return_id.

One row per customer return received by a seller; seller_id always names a seller.

Example

The rows must come back in the order shown.

Seller

seller_idseller_name
1Metro Gadgets
2Desi Weaves
3Pixel Point
4Kirana Kart

SellerReturn

return_idseller_idreturned_on
112024-10-01
212024-10-05
312024-10-08
412024-10-10
522024-10-03
622024-10-03
722024-10-09
832024-10-02
932024-10-09
1032024-10-16
3 more rows

Output

seller_nameflagged_on
Desi Weaves2024-10-09
Metro Gadgets2024-10-10
Kirana Kart2024-10-12
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.

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

ColumnType
seller_idint
seller_namevarchar

Primary key: seller_id.

One row per seller.

Table: SellerReturn

ColumnType
return_idint
seller_idint
returned_ondate

Primary key: return_id.

One row per customer return received by a seller; seller_id always names a seller.

Examples

Example 1

Seller

seller_idseller_name
1Metro Gadgets
2Desi Weaves
3Pixel Point
4Kirana Kart

SellerReturn

return_idseller_idreturned_on
112024-10-01
212024-10-05
312024-10-08
412024-10-10
522024-10-03
622024-10-03
722024-10-09
832024-10-02
932024-10-09
1032024-10-16
1142024-10-12
1242024-10-12
1342024-10-12

Output

seller_nameflagged_on
Desi Weaves2024-10-09
Metro Gadgets2024-10-10
Kirana Kart2024-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_name

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

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