Walmart: Substitutions Made Against the Customer's Wishes
When an item in an online grocery order is out of stock, a Walmart personal shopper may pick a substitute — but customers can say no.
- Difficulty: Medium
- Topics: Joins, Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #1009
Problem statement
When an item in an online grocery order is out of stock, a Walmart personal shopper may pick a substitute — but customers can say no. The online grocery quality team wants to coach the pickers who substitute when the customer asked them not to.
Each customer has a default setting (allow_subs), and may override it for particular items in SubPreference. For an order line, the item-level preference wins when one exists; otherwise the customer's default applies. A line with outcome substituted is unwanted when that setting is 0. For each picker with at least one unwanted substitution, return picker_id, unwanted_subs (such lines) and orders_affected (distinct orders containing one). Order by unwanted_subs descending, then picker_id ascending.
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: PickupCustomer
| Column | Type |
|---|---|
| customer_id | int |
| allow_subs | bool |
Primary key: customer_id.
One row per online grocery customer; allow_subs is 1 when substitutions are allowed by default.
Table: SubPreference
| Column | Type |
|---|---|
| customer_id | int |
| item_id | int |
| allow_sub | bool |
Primary key: customer_id, item_id.
A customer's override for one item: 1 allows a substitute for it, 0 forbids one.
Table: GroceryOrder
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| picker_id | int |
Primary key: order_id.
One row per online grocery order and the associate who picked it.
Table: OrderLine
| Column | Type |
|---|---|
| order_id | int |
| line_no | int |
| item_id | int |
| outcome | enum(picked, substituted, out_of_stock) |
Primary key: order_id, line_no.
One row per item the customer ordered and what the picker did with it.
Examples
Example 1
PickupCustomer
| customer_id | allow_subs |
|---|---|
| 71 | 1 |
| 72 | 0 |
| 73 | 1 |
SubPreference
| customer_id | item_id | allow_sub |
|---|---|---|
| 71 | 9001 | 0 |
| 72 | 9002 | 1 |
GroceryOrder
| order_id | customer_id | picker_id |
|---|---|---|
| 1001 | 71 | 41 |
| 1002 | 72 | 41 |
| 1003 | 73 | 42 |
| 1004 | 72 | 42 |
| 1005 | 73 | 43 |
OrderLine
| order_id | line_no | item_id | outcome |
|---|---|---|---|
| 1001 | 1 | 9001 | substituted |
| 1001 | 2 | 9002 | substituted |
| 1002 | 1 | 9002 | substituted |
| 1002 | 2 | 9003 | substituted |
| 1002 | 3 | 9004 | out_of_stock |
| 1003 | 1 | 9001 | substituted |
| 1004 | 1 | 9005 | substituted |
| 1004 | 2 | 9006 | substituted |
| 1004 | 3 | 9007 | picked |
| 1005 | 1 | 9003 | picked |
Output
| picker_id | unwanted_subs | orders_affected |
|---|---|---|
| 41 | 2 | 2 |
| 42 | 2 | 1 |
How to solve Walmart: Substitutions Made Against the Customer's Wishes
The heart of the problem is a precedence rule between two settings stored in different tables: an item-level override, when the customer set one, beats their account default. Joining each substituted line to its order gives the picker and the customer; joining the customer gives the default; and a LEFT JOIN to SubPreference on customer and item gives the override or NULL. An inner join there would silently drop every line without an override — most of them.
COALESCE(p.allow_sub, c.allow_subs) then reads as the rule itself: the override if present, otherwise the default. Because allow_sub is never NULL in a stored override, a NULL can only mean "no override", so the coalesce is exact. A line is unwanted when the effective setting is 0. Note the two directions: an override of 1 makes a substitution fine for a customer whose default is 0, and an override of 0 makes it unwanted for a customer who otherwise allows them.
Grouping by picker counts the lines and, with COUNT(DISTINCT order_id), the orders they touched; pickers with no unwanted substitution never form a group. The first alternative splits the rule into its two cases — a forbidding override, or no override and a forbidding default (NOT EXISTS) — and adds them with UNION ALL; the second spells the precedence with CASE. Each is a handful of key lookups per order line.
Reference solution (MySQL)
SELECT o.picker_id,
COUNT(*) AS unwanted_subs,
COUNT(DISTINCT o.order_id) AS orders_affected
FROM OrderLine l
JOIN GroceryOrder o ON o.order_id = l.order_id
JOIN PickupCustomer c ON c.customer_id = o.customer_id
LEFT JOIN SubPreference p ON p.customer_id = o.customer_id AND p.item_id = l.item_id
WHERE l.outcome = 'substituted'
AND COALESCE(p.allow_sub, c.allow_subs) = 0
GROUP BY o.picker_id
ORDER BY unwanted_subs DESC, o.picker_idAnother way
SELECT picker_id, COUNT(*) AS unwanted_subs, COUNT(DISTINCT order_id) AS orders_affected
FROM (
SELECT o.picker_id, o.order_id
FROM OrderLine l
JOIN GroceryOrder o ON o.order_id = l.order_id
JOIN SubPreference p ON p.customer_id = o.customer_id AND p.item_id = l.item_id
WHERE l.outcome = 'substituted' AND p.allow_sub = 0
UNION ALL
SELECT o.picker_id, o.order_id
FROM OrderLine l
JOIN GroceryOrder o ON o.order_id = l.order_id
JOIN PickupCustomer c ON c.customer_id = o.customer_id
WHERE l.outcome = 'substituted' AND c.allow_subs = 0
AND NOT EXISTS (SELECT 1 FROM SubPreference p WHERE p.customer_id = o.customer_id AND p.item_id = l.item_id)
) u
GROUP BY picker_id
ORDER BY unwanted_subs DESC, picker_idAnother way
SELECT o.picker_id, COUNT(*) AS unwanted_subs, COUNT(DISTINCT o.order_id) AS orders_affected
FROM OrderLine l
JOIN GroceryOrder o ON o.order_id = l.order_id
JOIN PickupCustomer c ON c.customer_id = o.customer_id
LEFT JOIN SubPreference p ON p.customer_id = o.customer_id AND p.item_id = l.item_id
WHERE l.outcome = 'substituted'
AND (CASE WHEN p.customer_id IS NOT NULL THEN p.allow_sub ELSE c.allow_subs END) = 0
GROUP BY o.picker_id
ORDER BY COUNT(*) DESC, o.picker_id← Walmart: Clearance Items Marked Down Three Times With No Sale · Walmart: Pickup Slot Fill Rate by Hour of Day →