Flipkart: F-Assured Sellers Missing the 24-Hour Dispatch SLA
F-Assured sellers must hand an order to Ekart within 24 hours of confirming it. The seller-quality team runs the check with the cut-off 2024-10-10 00:00:00.
- Difficulty: Medium
- Topics: Joins, Aggregation, Dates
- Dialect: MySQL
- Problem: #661
Problem statement
F-Assured sellers must hand an order to Ekart within 24 hours of confirming it. The seller-quality team runs the check with the cut-off 2024-10-10 00:00:00.
An order is late if it was dispatched more than 24 hours after confirmed_at, or if it has not been dispatched and the cut-off is more than 24 hours after confirmed_at. Exactly 24 hours is on time. Report a seller with at least 3 orders of which more than half are late. Return seller_name, orders (all their orders) and late_orders, ordered by late_orders descending, 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 F-Assured seller; names are unique.
Table: FulfilmentOrder
| Column | Type |
|---|---|
| order_id | int |
| seller_id | int |
| confirmed_at | datetime |
| dispatched_at | datetime |
Primary key: order_id.
One row per order, confirmed before the cut-off. dispatched_at is NULL until the parcel is handed over.
Examples
Example 1
Seller
| seller_id | seller_name |
|---|---|
| 1 | Metro Gadgets |
| 2 | Desi Weaves |
| 3 | Om Sai Traders |
FulfilmentOrder
| order_id | seller_id | confirmed_at | dispatched_at |
|---|---|---|---|
| 1 | 1 | 2024-10-01 10:00:00 | 2024-10-02 10:00:00 |
| 2 | 1 | 2024-10-02 09:00:00 | 2024-10-03 09:01:00 |
| 3 | 1 | 2024-10-07 18:30:00 | NULL |
| 4 | 2 | 2024-10-03 11:00:00 | 2024-10-03 15:00:00 |
| 5 | 2 | 2024-10-04 11:00:00 | 2024-10-06 12:00:00 |
| 6 | 2 | 2024-10-09 20:00:00 | NULL |
| 7 | 3 | 2024-10-05 08:00:00 | 2024-10-07 08:00:00 |
| 8 | 3 | 2024-10-05 09:00:00 | 2024-10-07 09:00:00 |
Output
| seller_name | orders | late_orders |
|---|---|---|
| Metro Gadgets | 3 | 2 |
How to solve Flipkart: F-Assured Sellers Missing the 24-Hour Dispatch SLA
The lateness test has two cases that collapse into one: an order still waiting is late precisely when the cut-off is already past its 24-hour deadline, which is the same as pretending it was dispatched at the cut-off. COALESCE(dispatched_at, '2024-10-10 00:00:00') makes that substitution, and TIMESTAMPDIFF(MINUTE, confirmed_at, …) > 1440 is the test. Measuring in minutes keeps the boundary exact — 24 hours to the minute is on time, 24 hours and one minute is late.
Per seller, COUNT(*) gives all orders and a CASE inside SUM the late ones. The two filters are on aggregates: at least three orders, so one bad day does not flag a seller, and 2 * late > orders for "more than half" without division or rounding.
The alternative compares the deadline DATE_ADD(confirmed_at, INTERVAL 24 HOUR) with the dispatch time (or the cut-off), sums the boolean flag, and compares with COUNT(*) / 2, which is decimal division. Both read each order once.
Reference solution (MySQL)
SELECT s.seller_name,
COUNT(*) AS orders,
SUM(CASE WHEN TIMESTAMPDIFF(MINUTE, o.confirmed_at, COALESCE(o.dispatched_at, '2024-10-10 00:00:00')) > 1440
THEN 1 ELSE 0 END) AS late_orders
FROM Seller s
JOIN FulfilmentOrder o ON o.seller_id = s.seller_id
GROUP BY s.seller_id, s.seller_name
HAVING COUNT(*) >= 3
AND 2 * SUM(CASE WHEN TIMESTAMPDIFF(MINUTE, o.confirmed_at, COALESCE(o.dispatched_at, '2024-10-10 00:00:00')) > 1440 THEN 1 ELSE 0 END) > COUNT(*)
ORDER BY late_orders DESC, s.seller_nameAnother way
WITH flagged AS (
SELECT seller_id,
CASE WHEN dispatched_at IS NULL THEN DATE_ADD(confirmed_at, INTERVAL 24 HOUR) < '2024-10-10 00:00:00'
ELSE DATE_ADD(confirmed_at, INTERVAL 24 HOUR) < dispatched_at END AS is_late
FROM FulfilmentOrder
)
SELECT s.seller_name, COUNT(*) AS orders, SUM(f.is_late) AS late_orders
FROM flagged f JOIN Seller s ON s.seller_id = f.seller_id
GROUP BY s.seller_id, s.seller_name
HAVING COUNT(*) >= 3 AND SUM(f.is_late) > COUNT(*) / 2
ORDER BY late_orders DESC, s.seller_name← Flipkart: Certified Buyer Reviews and Their Rating per Product · Flipkart: Sale Shoppers Who Bought From Three Categories →