Walmart: Understaffed Stretches on the Sales Floor
A Walmart store manager compares the staffing plan for 2025-07-12 with the associates who actually clocked in, and wants the stretches of the day when the…
- Difficulty: Hard
- Topics: Joins, Window Functions, Aggregation
- Dialect: MySQL
- Problem: #1020
Problem statement
A Walmart store manager compares the staffing plan for 2025-07-12 with the associates who actually clocked in, and wants the stretches of the day when the floor was short.
An associate's shift covers a plan hour h when it clocked in at or before h:00 on 2025-07-12 and clocked out at or after (h+1):00 the same day. An hour is understaffed when fewer shifts cover it than required. Merge each store's understaffed hours into stretches of consecutive clock hours (12 and 13 are consecutive; 11 and 13 are not, even if 12 is not in the plan). Return store_id, from_hour, to_hour (the stretch's first and last understaffed hour) and shortfall (the sum over its hours of required minus covering shifts), ordered by store_id, then from_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: StaffingPlan
| Column | Type |
|---|---|
| store_id | int |
| plan_hour | int |
| required | int |
Primary key: store_id, plan_hour.
The plan for 2025-07-12: how many associates each store needs on the floor in the hour starting at plan_hour (6 to 21).
Table: FloorShift
| Column | Type |
|---|---|
| shift_id | int |
| store_id | int |
| associate_id | int |
| clock_in | datetime |
| clock_out | datetime |
Primary key: shift_id.
One row per completed shift; the table also holds shifts from other days.
Examples
Example 1
StaffingPlan
| store_id | plan_hour | required |
|---|---|---|
| 1204 | 8 | 2 |
| 1204 | 9 | 2 |
| 1204 | 10 | 3 |
| 1204 | 11 | 3 |
| 1204 | 12 | 3 |
| 1204 | 13 | 3 |
| 3318 | 8 | 1 |
| 3318 | 9 | 1 |
| 3318 | 11 | 1 |
FloorShift
| shift_id | store_id | associate_id | clock_in | clock_out |
|---|---|---|---|---|
| 1 | 1204 | 41 | 2025-07-12 07:00:00 | 2025-07-12 12:00:00 |
| 2 | 1204 | 42 | 2025-07-12 08:30:00 | 2025-07-12 16:00:00 |
| 3 | 1204 | 43 | 2025-07-12 10:00:00 | 2025-07-12 14:00:00 |
| 4 | 1204 | 44 | 2025-07-11 09:00:00 | 2025-07-11 17:00:00 |
| 5 | 3318 | 51 | 2025-07-12 07:30:00 | 2025-07-12 09:00:00 |
Output
| store_id | from_hour | to_hour | shortfall |
|---|---|---|---|
| 1204 | 8 | 8 | 1 |
| 1204 | 12 | 13 | 2 |
| 3318 | 9 | 9 | 1 |
| 3318 | 11 | 11 | 1 |
How to solve Walmart: Understaffed Stretches on the Sales Floor
The problem has two stages: count coverage per plan hour, then merge the short hours into runs.
Coverage starts from StaffingPlan, because an hour that nobody covered is the most understaffed of all and must still produce a row; a LEFT JOIN to the shifts and COUNT(f.shift_id) gives 0 there. The join condition encodes "covers": the shift began at or before the hour's start and ended at or after its end, both as datetimes on 2025-07-12, so a shift from the previous day — same store, same clock times — never counts. A shift that clocks out at exactly 09:00 covers the 8 o'clock hour but not the 9 o'clock one.
The understaffed hours are then a gaps-and-islands problem. Along a run of consecutive hours, plan_hour and ROW_NUMBER() both rise by 1, so their difference is constant; a missing hour — whether staffed or simply not in the plan — changes it. Grouping by store and that difference gives each stretch, with MIN/MAX for its ends and SUM for the shortfall. Plan hours start at 6, so the difference never goes negative (MySQL's ROW_NUMBER is unsigned).
The alternative counts coverage with a correlated subquery and DATE_ADD for the hour boundaries, and finds stretch starts with LAG — a new stretch whenever the previous short hour is not exactly one hour earlier — numbered by a running sum.
Reference solution (MySQL)
WITH cover AS (
SELECT p.store_id, p.plan_hour, p.required, COUNT(f.shift_id) AS staffed
FROM StaffingPlan p
LEFT JOIN FloorShift f
ON f.store_id = p.store_id
AND f.clock_in <= CONCAT('2025-07-12 ', LPAD(p.plan_hour, 2, '0'), ':00:00')
AND f.clock_out >= CONCAT('2025-07-12 ', LPAD(p.plan_hour + 1, 2, '0'), ':00:00')
GROUP BY p.store_id, p.plan_hour, p.required
), short AS (
SELECT store_id, plan_hour, required - staffed AS gap,
plan_hour - ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY plan_hour) AS grp
FROM cover
WHERE staffed < required
)
SELECT store_id, MIN(plan_hour) AS from_hour, MAX(plan_hour) AS to_hour, SUM(gap) AS shortfall
FROM short
GROUP BY store_id, grp
ORDER BY store_id, from_hourAnother way
WITH cover AS (
SELECT p.store_id, p.plan_hour,
p.required - (SELECT COUNT(*) FROM FloorShift f
WHERE f.store_id = p.store_id
AND f.clock_in <= DATE_ADD('2025-07-12 00:00:00', INTERVAL p.plan_hour HOUR)
AND f.clock_out >= DATE_ADD('2025-07-12 00:00:00', INTERVAL p.plan_hour + 1 HOUR)) AS gap
FROM StaffingPlan p
), flagged AS (
SELECT store_id, plan_hour, gap,
CASE WHEN LAG(plan_hour) OVER (PARTITION BY store_id ORDER BY plan_hour) = plan_hour - 1 THEN 0 ELSE 1 END AS starts
FROM cover WHERE gap > 0
), numbered AS (
SELECT store_id, plan_hour, gap,
SUM(starts) OVER (PARTITION BY store_id ORDER BY plan_hour ROWS UNBOUNDED PRECEDING) AS stretch
FROM flagged
)
SELECT store_id, MIN(plan_hour) AS from_hour, MAX(plan_hour) AS to_hour, SUM(gap) AS shortfall
FROM numbered
GROUP BY store_id, stretch
ORDER BY store_id, from_hour← Walmart: Department Pairs Bought Together More Than by Chance · Walmart: Median Minutes From Shelf Gap to Refill by Department →