Walmart: Pickup Slot Fill Rate by Hour of Day — SQL Problem
The online grocery planning team at Walmart sizes pickup capacity by hour of day: hours that fill up need more associates picking, hours that sit empty can…
- Difficulty: Medium
- Topics: Joins, Aggregation, Subqueries
- Dialect: MySQL
- Problem: #1010
Problem statement
The online grocery planning team at Walmart sizes pickup capacity by hour of day: hours that fill up need more associates picking, hours that sit empty can lose windows.
For every start_hour that has at least one pickup window, return start_hour, capacity (the total capacity of the windows at that hour), booked (the number of their bookings with status booked; cancelled ones do not count) and fill_pct (booked as a percentage of capacity, rounded to 2 decimals). Hours with windows but no bookings show booked 0. Order by start_hour.
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: PickupWindow
| Column | Type |
|---|---|
| window_id | int |
| store_id | int |
| window_date | date |
| start_hour | int |
| capacity | int |
Primary key: window_id.
One row per pickup window a store offered; capacity is how many orders it can hand over.
Table: PickupBooking
| Column | Type |
|---|---|
| booking_id | int |
| window_id | int |
| customer_id | int |
| status | enum(booked, cancelled) |
Primary key: booking_id.
One row per customer booking of a window.
Examples
Example 1
PickupWindow
| window_id | store_id | window_date | start_hour | capacity |
|---|---|---|---|---|
| 1 | 1204 | 2025-04-12 | 9 | 4 |
| 2 | 3318 | 2025-04-12 | 9 | 2 |
| 3 | 1204 | 2025-04-12 | 12 | 5 |
| 4 | 1204 | 2025-04-13 | 12 | 3 |
| 5 | 3318 | 2025-04-12 | 17 | 6 |
PickupBooking
| booking_id | window_id | customer_id | status |
|---|---|---|---|
| 100 | 1 | 71 | booked |
| 101 | 1 | 72 | booked |
| 102 | 1 | 73 | cancelled |
| 103 | 2 | 74 | booked |
| 104 | 3 | 75 | booked |
| 105 | 5 | 76 | cancelled |
| 106 | 2 | 77 | booked |
Output
| start_hour | capacity | booked | fill_pct |
|---|---|---|---|
| 9 | 6 | 4 | 66.67 |
| 12 | 8 | 1 | 12.5 |
| 17 | 6 | 0 | 0 |
How to solve Walmart: Pickup Slot Fill Rate by Hour of Day
The trap here is join fan-out. If you join windows to bookings and group by hour, a window with three bookings appears three times, and SUM(capacity) adds its capacity three times — the fill rate comes out far too low. A window with no bookings disappears altogether under an inner join.
The fix is to aggregate each side at the right grain before combining them. One CTE sums capacity per hour straight from PickupWindow, where every window is one row. Another counts live bookings per hour by joining bookings to their window just to learn the hour. The final LEFT JOIN from capacity to bookings keeps every hour that has a window, and COALESCE(booked, 0) turns a missing count into 0.
Multiply by 100 before dividing so the percentage keeps its decimals, then round to two places. The alternative avoids the fan-out differently: a correlated subquery counts the bookings of each window, so each window is still exactly one row when the outer query groups by hour.
Reference solution (MySQL)
WITH cap AS (
SELECT start_hour, SUM(capacity) AS capacity
FROM PickupWindow
GROUP BY start_hour
), bk AS (
SELECT w.start_hour, COUNT(*) AS booked
FROM PickupBooking b
JOIN PickupWindow w ON w.window_id = b.window_id
WHERE b.status = 'booked'
GROUP BY w.start_hour
)
SELECT c.start_hour, c.capacity, COALESCE(bk.booked, 0) AS booked,
ROUND(100 * COALESCE(bk.booked, 0) / c.capacity, 2) AS fill_pct
FROM cap c
LEFT JOIN bk ON bk.start_hour = c.start_hour
ORDER BY c.start_hourAnother way
SELECT start_hour, SUM(capacity) AS capacity, SUM(n) AS booked, ROUND(100 * SUM(n) / SUM(capacity), 2) AS fill_pct
FROM (
SELECT w.start_hour, w.capacity,
(SELECT COUNT(*) FROM PickupBooking b WHERE b.window_id = w.window_id AND b.status = 'booked') AS n
FROM PickupWindow w
) t
GROUP BY start_hour
ORDER BY start_hour← Walmart: Substitutions Made Against the Customer's Wishes · Walmart: Close-Then-Open Shifts With Under Ten Hours' Rest →