Swiggy: Saved Addresses Without a Valid Pincode
Orders sent to an address with a broken pincode get routed to the wrong delivery zone, so the address-quality team wants every bad saved address listed for…
- Difficulty: Easy
- Topics: Strings
- Dialect: MySQL
- Problem: #951
Problem statement
Orders sent to an address with a broken pincode get routed to the wrong delivery zone, so the address-quality team wants every bad saved address listed for a fix-it prompt in the app.
A pincode is valid when it is exactly six digits and does not start with 0 — no spaces, no letters. Return address_id, customer_id and pincode for every saved address whose pincode is not valid, including addresses with no pincode at all (NULL). Order the rows by address_id.
A practice scenario set at Swiggy: the tables and data are invented, and this is not a question from, or endorsed by, Swiggy.
Tables
Table: SavedAddress
| Column | Type |
|---|---|
| address_id | int |
| customer_id | int |
| label | enum(Home, Work, Other) |
| pincode | varchar |
Primary key: address_id.
One row per address a customer saved. pincode is stored exactly as typed, or NULL when it was left empty.
Examples
Example 1
SavedAddress
| address_id | customer_id | label | pincode |
|---|---|---|---|
| 1 | 101 | Home | 560034 |
| 2 | 101 | Work | 56003 |
| 3 | 102 | Home | 060034 |
| 4 | 103 | Other | NULL |
| 5 | 104 | Home | 400 076 |
| 6 | 105 | Work | 5600341 |
| 7 | 106 | Home | 110O17 |
| 8 | 107 | Home | 500081 |
Output
| address_id | customer_id | pincode |
|---|---|---|
| 2 | 101 | 56003 |
| 3 | 102 | 060034 |
| 4 | 103 | NULL |
| 5 | 104 | 400 076 |
| 6 | 105 | 5600341 |
| 7 | 106 | 110O17 |
How to solve Swiggy: Saved Addresses Without a Valid Pincode
It is easier to describe a good pincode than every way of breaking one, so write the valid shape as a regular expression and negate it. ^[1-9][0-9]{5}$ reads: the string starts, one digit from 1 to 9, exactly five more digits, the string ends. The anchors matter — without them 5600341 would contain a six-digit match and pass. A space (400 076), a letter O typed for a zero, a leading zero or a short code all fail the pattern.
The NULL rows need their own test. pincode NOT REGEXP … on NULL yields NULL, and WHERE keeps only true, so without pincode IS NULL the empty addresses would silently vanish from a report that is supposed to find them. The second alternative folds that into one expression with COALESCE(…, 0) = 0.
The first alternative spells the rule out without a full pattern: wrong length, a leading zero, or any non-digit character. It is longer but shows each failure mode separately. Every version is one scan of the table.
Reference solution (MySQL)
SELECT address_id, customer_id, pincode
FROM SavedAddress
WHERE pincode IS NULL
OR pincode NOT REGEXP '^[1-9][0-9]{5}$'
ORDER BY address_idAnother way
SELECT address_id, customer_id, pincode FROM SavedAddress WHERE pincode IS NULL OR CHAR_LENGTH(pincode) <> 6 OR LEFT(pincode, 1) = '0' OR pincode REGEXP '[^0-9]' ORDER BY address_idAnother way
SELECT address_id, customer_id, pincode FROM SavedAddress WHERE COALESCE(pincode REGEXP '^[1-9][0-9]{5}$', 0) = 0 ORDER BY address_id← Swiggy: Genie Fare by Distance Slab · Swiggy: Swiggy One Plans With No Order Placed →