Walmart: Shelf Gaps Still Open at the Afternoon Walk
Associates at Walmart stores scan the shelves on a morning walk and again on an afternoon walk.
- Difficulty: Medium
- Topics: Joins
- Dialect: MySQL
- Problem: #1007
Problem statement
Associates at Walmart stores scan the shelves on a morning walk and again on an afternoon walk. The replenishment team wants every item that was missing from the shelf in the morning and still missing in the afternoon of the same day — a gap the back room did not fill.
Return store_id, item_id and walk_date for each store, item and day where the morning scan has on_shelf = 0 and the afternoon scan of the same store, item and day also has on_shelf = 0. If either walk has no scan for that item that day, it is not reported. 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: ShelfWalk
| Column | Type |
|---|---|
| walk_id | int |
| store_id | int |
| item_id | int |
| walk_date | date |
| walk | enum(morning, afternoon) |
| on_shelf | bool |
Primary key: walk_id.
One row per item scanned on a walk; there is at most one scan per store, item, day and walk. on_shelf is 1 when the item was on the shelf.
Examples
Example 1
ShelfWalk
| walk_id | store_id | item_id | walk_date | walk | on_shelf |
|---|---|---|---|---|---|
| 1 | 1204 | 501 | 2025-06-02 | morning | 0 |
| 2 | 1204 | 501 | 2025-06-02 | afternoon | 0 |
| 3 | 1204 | 502 | 2025-06-02 | morning | 0 |
| 4 | 1204 | 502 | 2025-06-02 | afternoon | 1 |
| 5 | 1204 | 503 | 2025-06-02 | morning | 1 |
| 6 | 1204 | 503 | 2025-06-02 | afternoon | 0 |
| 7 | 1204 | 504 | 2025-06-02 | morning | 0 |
| 8 | 3318 | 501 | 2025-06-02 | morning | 0 |
| 9 | 3318 | 501 | 2025-06-03 | afternoon | 0 |
| 10 | 3318 | 501 | 2025-06-03 | morning | 0 |
Output
| store_id | item_id | walk_date |
|---|---|---|
| 1204 | 501 | 2025-06-02 |
| 3318 | 501 | 2025-06-03 |
How to solve Walmart: Shelf Gaps Still Open at the Afternoon Walk
Each fact the question needs — the morning result and the afternoon result — lives on a different row, so the rows must be lined up side by side. A self join does that: alias m reads only morning scans, alias a only afternoon scans, and the join matches them on store, item and date. With at most one scan per walk, each morning row meets at most one afternoon row, so there are no duplicates.
Once paired, the condition is simply that both say on_shelf = 0. A morning gap that was filled by the afternoon, an item that went missing only in the afternoon, and a day where one walk never scanned the item all fall away — the last because the inner join finds no partner.
The grouped alternative collapses each store-item-day into one group and counts the two kinds of empty scan with conditional sums; it never compares rows directly. The EXISTS version keeps morning gaps that have a matching empty afternoon scan. With an index on (store_id, item_id, walk_date) all three are a single pass plus cheap lookups.
Reference solution (MySQL)
SELECT m.store_id, m.item_id, m.walk_date
FROM ShelfWalk m
JOIN ShelfWalk a
ON a.store_id = m.store_id AND a.item_id = m.item_id
AND a.walk_date = m.walk_date AND a.walk = 'afternoon'
WHERE m.walk = 'morning' AND m.on_shelf = 0 AND a.on_shelf = 0Another way
SELECT store_id, item_id, walk_date
FROM ShelfWalk
GROUP BY store_id, item_id, walk_date
HAVING SUM(walk = 'morning' AND on_shelf = 0) = 1 AND SUM(walk = 'afternoon' AND on_shelf = 0) = 1Another way
SELECT m.store_id, m.item_id, m.walk_date FROM ShelfWalk m
WHERE m.walk = 'morning' AND m.on_shelf = 0
AND EXISTS (SELECT 1 FROM ShelfWalk a WHERE a.store_id = m.store_id AND a.item_id = m.item_id
AND a.walk_date = m.walk_date AND a.walk = 'afternoon' AND a.on_shelf = 0)← Walmart: Price Match Approval Rate by Competitor · Walmart: Clearance Items Marked Down Three Times With No Sale →