Flipkart: Pincodes Where COD Parcels Keep Coming Back
When a cash-on-delivery customer refuses the parcel, it goes back to the seller — an RTO (return to origin) that costs two shipping legs.
- Difficulty: Medium
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #658
Problem statement
When a cash-on-delivery customer refuses the parcel, it goes back to the seller — an RTO (return to origin) that costs two shipping legs. Ekart wants the pincodes where COD should be reconsidered.
Only finished COD shipments count: payment_mode = COD and outcome either delivered or rto (in-transit parcels are not finished). Report a pincode when it has at least 4 finished COD shipments and RTOs are at least 25% of them. Return city, pincode, finished_cod and rto_shipments, ordered by rto_shipments descending, then pincode.
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: EkartPincode
| Column | Type |
|---|---|
| pincode | int |
| city | varchar |
Primary key: pincode.
One row per pincode Ekart serves.
Table: Shipment
| Column | Type |
|---|---|
| shipment_id | int |
| pincode | int |
| payment_mode | enum(COD, Prepaid) |
| outcome | enum(delivered, rto, in_transit) |
Primary key: shipment_id.
One row per parcel; pincode always names a row of EkartPincode.
Examples
Example 1
EkartPincode
| pincode | city |
|---|---|
| 302017 | Jaipur |
| 560034 | Bengaluru |
| 700091 | Kolkata |
Shipment
| shipment_id | pincode | payment_mode | outcome |
|---|---|---|---|
| 1 | 302017 | COD | rto |
| 2 | 302017 | COD | delivered |
| 3 | 302017 | COD | delivered |
| 4 | 302017 | COD | delivered |
| 5 | 302017 | COD | in_transit |
| 6 | 560034 | COD | rto |
| 7 | 560034 | Prepaid | delivered |
| 8 | 560034 | COD | delivered |
| 9 | 560034 | Prepaid | rto |
| 10 | 700091 | COD | rto |
| 11 | 700091 | COD | rto |
| 12 | 700091 | COD | delivered |
| 13 | 700091 | COD | delivered |
| 14 | 700091 | COD | delivered |
Output
| city | pincode | finished_cod | rto_shipments |
|---|---|---|---|
| Kolkata | 700091 | 5 | 2 |
| Jaipur | 302017 | 4 | 1 |
How to solve Flipkart: Pincodes Where COD Parcels Keep Coming Back
First narrow the rows: prepaid parcels never face a doorstep refusal and in-transit parcels have no outcome yet, so WHERE payment_mode = 'COD' AND outcome IN ('delivered', 'rto') leaves exactly the finished COD shipments. Grouping by pincode (with its city from the join) gives COUNT(*) as finished_cod and a CASE inside SUM as rto_shipments.
Both conditions are on aggregates and go in HAVING: at least four finished shipments, so a single unlucky parcel cannot flag a pincode, and an RTO share of at least 25%. Writing the share as 4 * rto >= finished keeps it in integers — no rounding, and a pincode at exactly a quarter is included as "at least" requires.
The alternative counts inside a derived table with COUNT(CASE …) and filters with rto * 100 >= finished * 25 after the join. Both read the shipments once; the join to EkartPincode is a key lookup per group.
Reference solution (MySQL)
SELECT p.city, s.pincode,
COUNT(*) AS finished_cod,
SUM(CASE WHEN s.outcome = 'rto' THEN 1 ELSE 0 END) AS rto_shipments
FROM Shipment s
JOIN EkartPincode p ON p.pincode = s.pincode
WHERE s.payment_mode = 'COD' AND s.outcome IN ('delivered', 'rto')
GROUP BY p.city, s.pincode
HAVING COUNT(*) >= 4 AND 4 * SUM(CASE WHEN s.outcome = 'rto' THEN 1 ELSE 0 END) >= COUNT(*)
ORDER BY rto_shipments DESC, s.pincodeAnother way
SELECT p.city, t.pincode, t.finished_cod, t.rto_shipments
FROM (SELECT pincode,
COUNT(CASE WHEN outcome <> 'in_transit' THEN 1 END) AS finished_cod,
COUNT(CASE WHEN outcome = 'rto' THEN 1 END) AS rto_shipments
FROM Shipment WHERE payment_mode = 'COD' GROUP BY pincode) t
JOIN EkartPincode p ON p.pincode = t.pincode
WHERE t.finished_cod >= 4 AND t.rto_shipments * 100 >= t.finished_cod * 25
ORDER BY t.rto_shipments DESC, t.pincode← Flipkart: Months Members Burned More SuperCoins Than Earned · Flipkart: Products Rated Lower After Big Billion Days →