Flipkart: COD Orders Shipped to Pincodes Without COD

Easy
649.

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

ColumnType
pincodeint
cityvarchar
cod_enabledbool

Primary key: pincode.

One row per pincode Ekart delivers to; cod_enabled is 1 when the courier may collect cash there.

Table: CustomerOrder

ColumnTypeValues
order_idint
customer_namevarchar
pincodeint
payment_modeenumCOD, Prepaid, Pay Later
order_valueint

Primary key: order_id.

One row per order, with the delivery pincode the customer entered.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

EkartPincode

pincodecitycod_enabled
560034Bengaluru1
110092Delhi0
400076Mumbai1
682030Kochi0

CustomerOrder

order_idcustomer_namepincodepayment_modeorder_value
9001Aarav560034COD1499
9002Diya110092COD799
9003Kabir110092Prepaid2599
9004Meera302017COD349
9005Rohan682030Pay Later4999
9006Zara600042Prepaid999
9007Ishaan682030COD1299

Output

order_idpincodeissue
9002110092COD disabled
9004302017Not serviceable
9007682030COD disabled
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: 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

ColumnType
pincodeint
cityvarchar
cod_enabledbool

Primary key: pincode.

One row per pincode Ekart delivers to; cod_enabled is 1 when the courier may collect cash there.

Table: CustomerOrder

ColumnType
order_idint
customer_namevarchar
pincodeint
payment_modeenum(COD, Prepaid, Pay Later)
order_valueint

Primary key: order_id.

One row per order, with the delivery pincode the customer entered.

Examples

Example 1

EkartPincode

pincodecitycod_enabled
560034Bengaluru1
110092Delhi0
400076Mumbai1
682030Kochi0

CustomerOrder

order_idcustomer_namepincodepayment_modeorder_value
9001Aarav560034COD1499
9002Diya110092COD799
9003Kabir110092Prepaid2599
9004Meera302017COD349
9005Rohan682030Pay Later4999
9006Zara600042Prepaid999
9007Ishaan682030COD1299

Output

order_idpincodeissue
9002110092COD disabled
9004302017Not serviceable
9007682030COD 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 →