Amazon: Warehouse Bins Shared by Several Sellers
In a fulfilment centre, a storage bin can hold stock belonging to different marketplace sellers.
- Difficulty: Medium
- Topics: Aggregation, Strings
- Dialect: MySQL
- Problem: #584
Problem statement
In a fulfilment centre, a storage bin can hold stock belonging to different marketplace sellers. The inventory-control team lists those shared bins so that a picker scanning the wrong seller's unit is caught.
Return every bin that currently holds stock (units > 0) of at least two different sellers: bin_id, seller_count (distinct sellers with stock in it) and sellers, their names in alphabetical order, separated by ', ', each name once. Rows with 0 units are ignored. Return the rows in any order.
A practice scenario set at Amazon: the tables and data are invented, and this is not a question from, or endorsed by, Amazon.
Tables
Table: Seller
| Column | Type |
|---|---|
| seller_id | int |
| seller_name | varchar |
Primary key: seller_id.
One row per seller; names are unique.
Table: BinStock
| Column | Type |
|---|---|
| bin_id | varchar |
| fnsku | varchar |
| seller_id | int |
| units | int |
Primary key: bin_id, fnsku.
One row per product label (fnsku) stored in a bin; each fnsku belongs to one seller, and a seller may have several in one bin.
Examples
Example 1
Seller
| seller_id | seller_name |
|---|---|
| 1 | Sunrise Traders |
| 2 | Kaveri Retail |
| 3 | Blue Peak Store |
| 4 | Indus Mart |
BinStock
| bin_id | fnsku | seller_id | units |
|---|---|---|---|
| P1-A101 | X001KAV1 | 2 | 4 |
| P1-A101 | X001SUN1 | 1 | 2 |
| P1-A101 | X001SUN2 | 1 | 6 |
| P1-A102 | X001BLU1 | 3 | 3 |
| P1-A102 | X001IND1 | 4 | 0 |
| P1-A103 | X001IND2 | 4 | 1 |
| P1-A103 | X001BLU2 | 3 | 5 |
| P1-A103 | X001KAV2 | 2 | 2 |
Output
| bin_id | seller_count | sellers |
|---|---|---|
| P1-A101 | 2 | Kaveri Retail, Sunrise Traders |
| P1-A103 | 3 | Blue Peak Store, Indus Mart, Kaveri Retail |
How to solve Amazon: Warehouse Bins Shared by Several Sellers
Per bin, the question is how many different sellers own stock in it and who they are. Filtering units > 0 in WHERE removes labels that are recorded but empty, so a seller whose only row in a bin has zero units is not counted there. Grouping by bin_id then gives one row per bin.
A seller can have several labels (fnsku) in one bin, so both the count and the list must be distinct: COUNT(DISTINCT seller_id) and GROUP_CONCAT(DISTINCT seller_name ORDER BY seller_name SEPARATOR ', '). The ORDER BY inside GROUP_CONCAT fixes the order of the names, which otherwise depends on how rows happen to be read. HAVING keeps the bins with two or more sellers.
The alternative first reduces the data to distinct (bin, seller name) pairs in a derived table; after that a plain COUNT(*) and a plain GROUP_CONCAT are already distinct. Since seller names are unique, counting names equals counting ids. Both are one grouped scan.
Reference solution (MySQL)
SELECT b.bin_id,
COUNT(DISTINCT b.seller_id) AS seller_count,
GROUP_CONCAT(DISTINCT s.seller_name ORDER BY s.seller_name SEPARATOR ', ') AS sellers
FROM BinStock b
JOIN Seller s ON s.seller_id = b.seller_id
WHERE b.units > 0
GROUP BY b.bin_id
HAVING COUNT(DISTINCT b.seller_id) >= 2Another way
SELECT bin_id, COUNT(*) AS seller_count, GROUP_CONCAT(seller_name ORDER BY seller_name SEPARATOR ', ') AS sellers
FROM (SELECT DISTINCT b.bin_id, s.seller_name FROM BinStock b JOIN Seller s ON s.seller_id = b.seller_id WHERE b.units > 0) t
GROUP BY bin_id HAVING COUNT(*) > 1← Amazon: Echo Buyers Who Added a Smart Plug Within 60 Days · Amazon: Kindle Reading Sessions That Ended Behind the Last One →