Flipkart: COD Orders Shipped to Pincodes Without COD
Ekart's serviceability team found cash-on-delivery orders slipping through to pincodes where couriers cannot collect cash.
- Difficulty: Easy
- Topics: Joins, Conditional Logic
- Dialect: MySQL
- Problem: #649
Problem statement
Ekart's serviceability team found cash-on-delivery orders slipping through to pincodes where couriers cannot collect cash. They want every such order listed with the reason.
Among orders with payment_mode = COD, return those whose pincode either is listed in EkartPincode with cod_enabled = 0, or is not listed at all (Ekart does not serve it). Return order_id, pincode and issue, where issue is 'COD disabled' for a listed pincode and 'Not serviceable' for an unlisted one. Prepaid and Pay Later orders are never reported. Return the rows in any order.
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 |
| cod_enabled | bool |
Primary key: pincode.
One row per pincode Ekart delivers to; cod_enabled is 1 when the courier may collect cash there.
Table: CustomerOrder
| Column | Type |
|---|---|
| order_id | int |
| customer_name | varchar |
| pincode | int |
| payment_mode | enum(COD, Prepaid, Pay Later) |
| order_value | int |
Primary key: order_id.
One row per order, with the delivery pincode the customer entered.
Examples
Example 1
EkartPincode
| pincode | city | cod_enabled |
|---|---|---|
| 560034 | Bengaluru | 1 |
| 110092 | Delhi | 0 |
| 400076 | Mumbai | 1 |
| 682030 | Kochi | 0 |
CustomerOrder
| order_id | customer_name | pincode | payment_mode | order_value |
|---|---|---|---|---|
| 9001 | Aarav | 560034 | COD | 1499 |
| 9002 | Diya | 110092 | COD | 799 |
| 9003 | Kabir | 110092 | Prepaid | 2599 |
| 9004 | Meera | 302017 | COD | 349 |
| 9005 | Rohan | 682030 | Pay Later | 4999 |
| 9006 | Zara | 600042 | Prepaid | 999 |
| 9007 | Ishaan | 682030 | COD | 1299 |
Output
| order_id | pincode | issue |
|---|---|---|
| 9002 | 110092 | COD disabled |
| 9004 | 302017 | Not serviceable |
| 9007 | 682030 | COD disabled |
How to solve Flipkart: COD Orders Shipped to Pincodes Without COD
Two different failures share one report: a pincode Ekart serves without COD, and a pincode Ekart does not serve at all. A LEFT JOIN from orders to pincodes handles both in one pass — a listed pincode brings its cod_enabled flag along, and an unlisted one brings NULLs.
The filter keeps COD orders where either the join found nothing (p.pincode IS NULL) or it found the pincode with cod_enabled = 0. The parentheses matter: without them, AND would bind to only half the condition and Prepaid orders to unlisted pincodes would leak in. The issue column is then a CASE on the same NULL test.
The alternative writes the two failures as two queries — a semi join with IN and an anti join with NOT IN — glued with UNION ALL. NOT IN is safe here because pincode is the primary key of EkartPincode and can never be NULL. Either form is one scan of the orders with a key lookup each.
Reference solution (MySQL)
SELECT o.order_id, o.pincode,
CASE WHEN p.pincode IS NULL THEN 'Not serviceable' ELSE 'COD disabled' END AS issue
FROM CustomerOrder o
LEFT JOIN EkartPincode p ON p.pincode = o.pincode
WHERE o.payment_mode = 'COD'
AND (p.pincode IS NULL OR p.cod_enabled = 0)Another way
SELECT order_id, pincode, 'COD disabled' AS issue FROM CustomerOrder
WHERE payment_mode = 'COD' AND pincode IN (SELECT pincode FROM EkartPincode WHERE cod_enabled = 0)
UNION ALL
SELECT order_id, pincode, 'Not serviceable' FROM CustomerOrder
WHERE payment_mode = 'COD' AND pincode NOT IN (SELECT pincode FROM EkartPincode)← Flipkart: Plus Members Sitting on Unused SuperCoins · Flipkart: Active F-Assured Listings per Seller →