Walmart: Understaffed Stretches on the Sales Floor

Hard
1020.

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

ColumnType
store_idint
plan_hourint
requiredint

Primary key (together): 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

ColumnType
shift_idint
store_idint
associate_idint
clock_indatetime
clock_outdatetime

Primary key: shift_id.

One row per completed shift; the table also holds shifts from other days.

Example

The rows must come back in the order shown.

StaffingPlan

store_idplan_hourrequired
120482
120492
1204103
1204113
1204123
1204133
331881
331891
3318111

FloorShift

shift_idstore_idassociate_idclock_inclock_out
11204412025-07-12 07:00:002025-07-12 12:00:00
21204422025-07-12 08:30:002025-07-12 16:00:00
31204432025-07-12 10:00:002025-07-12 14:00:00
41204442025-07-11 09:00:002025-07-11 17:00:00
53318512025-07-12 07:30:002025-07-12 09:00:00

Output

store_idfrom_hourto_hourshortfall
1204881
120412132
3318991
331811111
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: 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

ColumnType
store_idint
plan_hourint
requiredint

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

ColumnType
shift_idint
store_idint
associate_idint
clock_indatetime
clock_outdatetime

Primary key: shift_id.

One row per completed shift; the table also holds shifts from other days.

Examples

Example 1

StaffingPlan

store_idplan_hourrequired
120482
120492
1204103
1204113
1204123
1204133
331881
331891
3318111

FloorShift

shift_idstore_idassociate_idclock_inclock_out
11204412025-07-12 07:00:002025-07-12 12:00:00
21204422025-07-12 08:30:002025-07-12 16:00:00
31204432025-07-12 10:00:002025-07-12 14:00:00
41204442025-07-11 09:00:002025-07-11 17:00:00
53318512025-07-12 07:30:002025-07-12 09:00:00

Output

store_idfrom_hourto_hourshortfall
1204881
120412132
3318991
331811111

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_hour

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