Amazon: Warehouse Bins Shared by Several Sellers

Medium
584.

Amazon: Warehouse Bins Shared by Several Sellers

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

ColumnType
seller_idint
seller_namevarchar

Primary key: seller_id.

One row per seller; names are unique.

Table: BinStock

ColumnType
bin_idvarchar
fnskuvarchar
seller_idint
unitsint

Primary key (together): 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.

Example

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

Seller

seller_idseller_name
1Sunrise Traders
2Kaveri Retail
3Blue Peak Store
4Indus Mart

BinStock

bin_idfnskuseller_idunits
P1-A101X001KAV124
P1-A101X001SUN112
P1-A101X001SUN216
P1-A102X001BLU133
P1-A102X001IND140
P1-A103X001IND241
P1-A103X001BLU235
P1-A103X001KAV222

Output

bin_idseller_countsellers
P1-A1012Kaveri Retail, Sunrise Traders
P1-A1033Blue Peak Store, Indus Mart, Kaveri Retail
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.

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

ColumnType
seller_idint
seller_namevarchar

Primary key: seller_id.

One row per seller; names are unique.

Table: BinStock

ColumnType
bin_idvarchar
fnskuvarchar
seller_idint
unitsint

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_idseller_name
1Sunrise Traders
2Kaveri Retail
3Blue Peak Store
4Indus Mart

BinStock

bin_idfnskuseller_idunits
P1-A101X001KAV124
P1-A101X001SUN112
P1-A101X001SUN216
P1-A102X001BLU133
P1-A102X001IND140
P1-A103X001IND241
P1-A103X001BLU235
P1-A103X001KAV222

Output

bin_idseller_countsellers
P1-A1012Kaveri Retail, Sunrise Traders
P1-A1033Blue 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) >= 2

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