Walmart: Grocery Pickup Slots Nobody Booked — SQL Problem
The online grocery team at Walmart opens hourly pickup slots at each store and wants to see which slots went completely unused, so it can trim the slot…
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #999
Problem statement
The online grocery team at Walmart opens hourly pickup slots at each store and wants to see which slots went completely unused, so it can trim the slot calendar.
Return every slot that has no order other than cancelled ones — a slot with no orders at all counts, and so does a slot whose only orders were cancelled. Return slot_id, store_id, slot_date and start_hour, 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: PickupSlot
| Column | Type |
|---|---|
| slot_id | int |
| store_id | int |
| slot_date | date |
| start_hour | int |
Primary key: slot_id.
One row per one-hour pickup slot; start_hour is 0–23 in store time.
Table: PickupOrder
| Column | Type |
|---|---|
| order_id | int |
| slot_id | int |
| customer_id | int |
| status | enum(scheduled, picked_up, cancelled) |
Primary key: order_id.
One row per online grocery order; slot_id is the slot the customer chose.
Examples
Example 1
PickupSlot
| slot_id | store_id | slot_date | start_hour |
|---|---|---|---|
| 1 | 1204 | 2025-04-12 | 9 |
| 2 | 1204 | 2025-04-12 | 10 |
| 3 | 1204 | 2025-04-12 | 11 |
| 4 | 1204 | 2025-04-12 | 12 |
| 5 | 3318 | 2025-04-12 | 9 |
| 6 | 3318 | 2025-04-12 | 10 |
PickupOrder
| order_id | slot_id | customer_id | status |
|---|---|---|---|
| 501 | 1 | 71 | picked_up |
| 502 | 2 | 72 | cancelled |
| 503 | 3 | 73 | cancelled |
| 504 | 3 | 74 | scheduled |
| 505 | 5 | 75 | scheduled |
| 506 | 5 | 76 | picked_up |
Output
| slot_id | store_id | slot_date | start_hour |
|---|---|---|---|
| 2 | 1204 | 2025-04-12 | 10 |
| 4 | 1204 | 2025-04-12 | 12 |
| 6 | 3318 | 2025-04-12 | 10 |
How to solve Walmart: Grocery Pickup Slots Nobody Booked
An unused slot is one for which no live order exists, which is the shape of an anti join: keep a slot when a matching row is absent.
NOT EXISTS states it directly. For each slot the subquery looks for an order on that slot whose status is not cancelled; if it finds none, the slot is kept. That covers both cases the team cares about — a slot nobody chose, and a slot whose every order was cancelled — without any special handling.
The LEFT JOIN version pairs each slot with its live orders and keeps the slots that found none (o.order_id IS NULL). The status test must be part of the join condition: placed in WHERE it would reject the NULL-extended rows and the query would return nothing useful. NOT IN works too because slot_id is never NULL in the orders; with a nullable column it would silently return no rows, which is why NOT EXISTS is the safer habit. With an index on PickupOrder(slot_id) each check is a short index probe.
Reference solution (MySQL)
SELECT s.slot_id, s.store_id, s.slot_date, s.start_hour
FROM PickupSlot s
WHERE NOT EXISTS (
SELECT 1 FROM PickupOrder o
WHERE o.slot_id = s.slot_id AND o.status <> 'cancelled'
)Another way
SELECT s.slot_id, s.store_id, s.slot_date, s.start_hour
FROM PickupSlot s
LEFT JOIN PickupOrder o ON o.slot_id = s.slot_id AND o.status <> 'cancelled'
WHERE o.order_id IS NULLAnother way
SELECT slot_id, store_id, slot_date, start_hour FROM PickupSlot WHERE slot_id NOT IN (SELECT slot_id FROM PickupOrder WHERE status IN ('scheduled', 'picked_up'))← Walmart: Active Rollbacks and the Savings on Each · Walmart: Active Walmart+ Members on 30 June by Plan →