Walmart: Substitutions Made Against the Customer's Wishes

Medium
1009.

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

ColumnType
customer_idint
allow_subsbool

Primary key: customer_id.

One row per online grocery customer; allow_subs is 1 when substitutions are allowed by default.

Table: SubPreference

ColumnType
customer_idint
item_idint
allow_subbool

Primary key (together): customer_id, item_id.

A customer's override for one item: 1 allows a substitute for it, 0 forbids one.

Table: GroceryOrder

ColumnType
order_idint
customer_idint
picker_idint

Primary key: order_id.

One row per online grocery order and the associate who picked it.

Table: OrderLine

ColumnTypeValues
order_idint
line_noint
item_idint
outcomeenumpicked, substituted, out_of_stock

Primary key (together): order_id, line_no.

One row per item the customer ordered and what the picker did with it.

Example

The rows must come back in the order shown.

PickupCustomer

customer_idallow_subs
711
720
731

SubPreference

customer_iditem_idallow_sub
7190010
7290021

GroceryOrder

order_idcustomer_idpicker_id
10017141
10027241
10037342
10047242
10057343

OrderLine

order_idline_noitem_idoutcome
100119001substituted
100129002substituted
100219002substituted
100229003substituted
100239004out_of_stock
100319001substituted
100419005substituted
100429006substituted
100439007picked
100519003picked

Output

picker_idunwanted_subsorders_affected
4122
4221
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: 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

ColumnType
customer_idint
allow_subsbool

Primary key: customer_id.

One row per online grocery customer; allow_subs is 1 when substitutions are allowed by default.

Table: SubPreference

ColumnType
customer_idint
item_idint
allow_subbool

Primary key: customer_id, item_id.

A customer's override for one item: 1 allows a substitute for it, 0 forbids one.

Table: GroceryOrder

ColumnType
order_idint
customer_idint
picker_idint

Primary key: order_id.

One row per online grocery order and the associate who picked it.

Table: OrderLine

ColumnType
order_idint
line_noint
item_idint
outcomeenum(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_idallow_subs
711
720
731

SubPreference

customer_iditem_idallow_sub
7190010
7290021

GroceryOrder

order_idcustomer_idpicker_id
10017141
10027241
10037342
10047242
10057343

OrderLine

order_idline_noitem_idoutcome
100119001substituted
100129002substituted
100219002substituted
100229003substituted
100239004out_of_stock
100319001substituted
100419005substituted
100429006substituted
100439007picked
100519003picked

Output

picker_idunwanted_subsorders_affected
4122
4221

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_id

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

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