Walmart: Item Records Sharing a UPC After Cleanup
A Walmart data engineer cleaning the item master finds UPCs typed in different ways — with spaces, with hyphens, and with leading zeros dropped — so one…
- Difficulty: Medium
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #1014
Problem statement
A Walmart data engineer cleaning the item master finds UPCs typed in different ways — with spaces, with hyphens, and with leading zeros dropped — so one product can sit under two item records.
Clean each non-NULL upc by removing every space and every hyphen and then left-padding it with zeros to 12 digits. Return each cleaned UPC that belongs to two or more items, as upc (the cleaned value) and item_ids (the item ids in ascending order, joined by commas with no spaces, e.g. 3,7,12). Items with a NULL upc are ignored. Return the rows in any order.
A practice scenario set at Walmart: the tables and data are invented, and this is not a question from, or endorsed by, Walmart.
Tables
Table: ItemMaster
| Column | Type |
|---|---|
| item_id | int |
| item_desc | varchar |
| upc | varchar |
Primary key: item_id.
One row per item record. Once spaces and hyphens are removed every upc is 10 to 12 digits.
Examples
Example 1
ItemMaster
| item_id | item_desc | upc |
|---|---|---|
| 3 | Peanut Butter 40 oz | 0 51500 24008 3 |
| 5 | Peanut Butter 40oz | 051500240083 |
| 7 | PEANUT BUTTER CRMY 40 | 51500240083 |
| 8 | Paper Plates 100 ct | 0-78742-37078-5 |
| 9 | Paper Plates 100ct | 078742370785 |
| 12 | AA Batteries 24 pk | 039800013613 |
| 14 | Dish Soap 28 oz | NULL |
| 15 | Dish Soap | NULL |
Output
| upc | item_ids |
|---|---|
| 051500240083 | 3,5,7 |
| 078742370785 | 8,9 |
How to solve Walmart: Item Records Sharing a UPC After Cleanup
Duplicates can only be found once every UPC is written the same way, so the query cleans first and groups second. Two nested REPLACE calls strip the spaces and the hyphens; what remains is a run of digits, sometimes short because a spreadsheet dropped its leading zeros. LPAD(…, 12, '0') restores them, so 51500240083 and 051500240083 become the same 12-character string.
Grouping by the cleaned value then puts all records of one product together, and HAVING COUNT(*) >= 2 keeps the products with more than one record. NULL UPCs are removed before grouping — otherwise all of them would form one group and be reported as a "duplicate" of each other, which says nothing about the products.
GROUP_CONCAT(item_id ORDER BY item_id SEPARATOR ',') produces the list in a fixed order, so the output does not depend on storage order. The alternative pads differently — prefix twelve zeros and keep the rightmost twelve characters — and relies on the default comma separator and on CONCAT returning NULL for a NULL UPC. Both are one scan plus a group-by.
Reference solution (MySQL)
WITH c AS (
SELECT item_id, LPAD(REPLACE(REPLACE(upc, ' ', ''), '-', ''), 12, '0') AS upc
FROM ItemMaster
WHERE upc IS NOT NULL
)
SELECT upc, GROUP_CONCAT(item_id ORDER BY item_id SEPARATOR ',') AS item_ids
FROM c
GROUP BY upc
HAVING COUNT(*) >= 2Another way
SELECT clean AS upc, GROUP_CONCAT(item_id ORDER BY item_id) AS item_ids
FROM (SELECT item_id, RIGHT(CONCAT('000000000000', REPLACE(REPLACE(upc, '-', ''), ' ', '')), 12) AS clean FROM ItemMaster) t
WHERE clean IS NOT NULL
GROUP BY clean
HAVING COUNT(DISTINCT item_id) > 1← Walmart: Repeat Receiptless Returns Across Stores · Walmart: Late Store Deliveries by Day of the Week →