Walmart: Item Records Sharing a UPC After Cleanup

Medium
1014.

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

ColumnType
item_idint
item_descvarchar
upcvarchar

Primary key: item_id.

One row per item record. Once spaces and hyphens are removed every upc is 10 to 12 digits.

Example

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

ItemMaster

item_iditem_descupc
3Peanut Butter 40 oz0 51500 24008 3
5Peanut Butter 40oz051500240083
7PEANUT BUTTER CRMY 4051500240083
8Paper Plates 100 ct0-78742-37078-5
9Paper Plates 100ct078742370785
12AA Batteries 24 pk039800013613
14Dish Soap 28 ozNULL
15Dish SoapNULL

Output

upcitem_ids
0515002400833,5,7
0787423707858,9
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.

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

ColumnType
item_idint
item_descvarchar
upcvarchar

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_iditem_descupc
3Peanut Butter 40 oz0 51500 24008 3
5Peanut Butter 40oz051500240083
7PEANUT BUTTER CRMY 4051500240083
8Paper Plates 100 ct0-78742-37078-5
9Paper Plates 100ct078742370785
12AA Batteries 24 pk039800013613
14Dish Soap 28 ozNULL
15Dish SoapNULL

Output

upcitem_ids
0515002400833,5,7
0787423707858,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(*) >= 2

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