Walmart: Shelf Gaps Still Open at the Afternoon Walk

Medium
1007.

Walmart: Shelf Gaps Still Open at the Afternoon Walk

MediumJoins

MySQL

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

ColumnTypeValues
walk_idint
store_idint
item_idint
walk_datedate
walkenummorning, afternoon
on_shelfbool

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.

Example

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

ShelfWalk

walk_idstore_iditem_idwalk_datewalkon_shelf
112045012025-06-02morning0
212045012025-06-02afternoon0
312045022025-06-02morning0
412045022025-06-02afternoon1
512045032025-06-02morning1
612045032025-06-02afternoon0
712045042025-06-02morning0
833185012025-06-02morning0
933185012025-06-03afternoon0
1033185012025-06-03morning0

Output

store_iditem_idwalk_date
12045012025-06-02
33185012025-06-03
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: 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

ColumnType
walk_idint
store_idint
item_idint
walk_datedate
walkenum(morning, afternoon)
on_shelfbool

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_idstore_iditem_idwalk_datewalkon_shelf
112045012025-06-02morning0
212045012025-06-02afternoon0
312045022025-06-02morning0
412045022025-06-02afternoon1
512045032025-06-02morning1
612045032025-06-02afternoon0
712045042025-06-02morning0
833185012025-06-02morning0
933185012025-06-03afternoon0
1033185012025-06-03morning0

Output

store_iditem_idwalk_date
12045012025-06-02
33185012025-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 = 0

Another 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) = 1

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