Flipkart: Pincodes Where COD Parcels Keep Coming Back

Medium
658.

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

ColumnType
pincodeint
cityvarchar

Primary key: pincode.

One row per pincode Ekart serves.

Table: Shipment

ColumnTypeValues
shipment_idint
pincodeint
payment_modeenumCOD, Prepaid
outcomeenumdelivered, rto, in_transit

Primary key: shipment_id.

One row per parcel; pincode always names a row of EkartPincode.

Example

The rows must come back in the order shown.

EkartPincode

pincodecity
302017Jaipur
560034Bengaluru
700091Kolkata

Shipment

shipment_idpincodepayment_modeoutcome
1302017CODrto
2302017CODdelivered
3302017CODdelivered
4302017CODdelivered
5302017CODin_transit
6560034CODrto
7560034Prepaiddelivered
8560034CODdelivered
9560034Prepaidrto
10700091CODrto
4 more rows

Output

citypincodefinished_codrto_shipments
Kolkata70009152
Jaipur30201741
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: 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

ColumnType
pincodeint
cityvarchar

Primary key: pincode.

One row per pincode Ekart serves.

Table: Shipment

ColumnType
shipment_idint
pincodeint
payment_modeenum(COD, Prepaid)
outcomeenum(delivered, rto, in_transit)

Primary key: shipment_id.

One row per parcel; pincode always names a row of EkartPincode.

Examples

Example 1

EkartPincode

pincodecity
302017Jaipur
560034Bengaluru
700091Kolkata

Shipment

shipment_idpincodepayment_modeoutcome
1302017CODrto
2302017CODdelivered
3302017CODdelivered
4302017CODdelivered
5302017CODin_transit
6560034CODrto
7560034Prepaiddelivered
8560034CODdelivered
9560034Prepaidrto
10700091CODrto
11700091CODrto
12700091CODdelivered
13700091CODdelivered
14700091CODdelivered

Output

citypincodefinished_codrto_shipments
Kolkata70009152
Jaipur30201741

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

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