Walmart: Grocery Pickup Slots Nobody Booked

Easy
999.

Walmart: Grocery Pickup Slots Nobody Booked

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

ColumnType
slot_idint
store_idint
slot_datedate
start_hourint

Primary key: slot_id.

One row per one-hour pickup slot; start_hour is 0–23 in store time.

Table: PickupOrder

ColumnTypeValues
order_idint
slot_idint
customer_idint
statusenumscheduled, picked_up, cancelled

Primary key: order_id.

One row per online grocery order; slot_id is the slot the customer chose.

Example

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

PickupSlot

slot_idstore_idslot_datestart_hour
112042025-04-129
212042025-04-1210
312042025-04-1211
412042025-04-1212
533182025-04-129
633182025-04-1210

PickupOrder

order_idslot_idcustomer_idstatus
501171picked_up
502272cancelled
503373cancelled
504374scheduled
505575scheduled
506576picked_up

Output

slot_idstore_idslot_datestart_hour
212042025-04-1210
412042025-04-1212
633182025-04-1210
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: 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

ColumnType
slot_idint
store_idint
slot_datedate
start_hourint

Primary key: slot_id.

One row per one-hour pickup slot; start_hour is 0–23 in store time.

Table: PickupOrder

ColumnType
order_idint
slot_idint
customer_idint
statusenum(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_idstore_idslot_datestart_hour
112042025-04-129
212042025-04-1210
312042025-04-1211
412042025-04-1212
533182025-04-129
633182025-04-1210

PickupOrder

order_idslot_idcustomer_idstatus
501171picked_up
502272cancelled
503373cancelled
504374scheduled
505575scheduled
506576picked_up

Output

slot_idstore_idslot_datestart_hour
212042025-04-1210
412042025-04-1212
633182025-04-1210

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 NULL

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