Walmart: Close-Then-Open Shifts With Under Ten Hours' Rest

Medium
1011.

Walmart: Close-Then-Open Shifts With Under Ten Hours' Rest

Walmart's store scheduling team watches for "clopens" — an associate who closes the store at night and is back to open it in the morning — because too little rest between shifts hurts both people and safety.

For each shift, the rest before it is the minutes between the end of the same associate's previous shift (the one with the latest clock_in before this shift's) and this shift's clock_in. Return associate_id, shift_id and rest_minutes for every shift with a previous shift and less than 600 minutes of rest. An associate's shifts never overlap. Order by associate_id, then shift_id.

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

ColumnType
shift_idint
associate_idint
clock_indatetime
clock_outdatetime

Primary key: shift_id.

One row per completed shift. Shift ids are not in time order — they were assigned when the schedule was entered.

Example

The rows must come back in the order shown.

AssociateShift

shift_idassociate_idclock_inclock_out
11412025-06-02 14:00:002025-06-02 23:00:00
12412025-06-03 07:00:002025-06-03 15:00:00
13412025-06-04 08:00:002025-06-04 16:00:00
10412025-06-05 02:00:002025-06-05 10:00:00
20422025-06-02 13:00:002025-06-02 22:00:00
21422025-06-03 08:00:002025-06-03 16:00:00
30432025-06-03 06:00:002025-06-03 14:00:00

Output

associate_idshift_idrest_minutes
4112480
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: Close-Then-Open Shifts With Under Ten Hours' Rest

Walmart's store scheduling team watches for "clopens" — an associate who closes the store at night and is back to open it in the morning — because too…

  • Difficulty: Medium
  • Topics: Window Functions, Dates
  • Dialect: MySQL
  • Problem: #1011

Problem statement

Walmart's store scheduling team watches for "clopens" — an associate who closes the store at night and is back to open it in the morning — because too little rest between shifts hurts both people and safety.

For each shift, the rest before it is the minutes between the end of the same associate's previous shift (the one with the latest clock_in before this shift's) and this shift's clock_in. Return associate_id, shift_id and rest_minutes for every shift with a previous shift and less than 600 minutes of rest. An associate's shifts never overlap. Order by associate_id, then shift_id.

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

ColumnType
shift_idint
associate_idint
clock_indatetime
clock_outdatetime

Primary key: shift_id.

One row per completed shift. Shift ids are not in time order — they were assigned when the schedule was entered.

Examples

Example 1

AssociateShift

shift_idassociate_idclock_inclock_out
11412025-06-02 14:00:002025-06-02 23:00:00
12412025-06-03 07:00:002025-06-03 15:00:00
13412025-06-04 08:00:002025-06-04 16:00:00
10412025-06-05 02:00:002025-06-05 10:00:00
20422025-06-02 13:00:002025-06-02 22:00:00
21422025-06-03 08:00:002025-06-03 16:00:00
30432025-06-03 06:00:002025-06-03 14:00:00

Output

associate_idshift_idrest_minutes
4112480

How to solve Walmart: Close-Then-Open Shifts With Under Ten Hours' Rest

Rest is measured between two rows — the end of one shift and the start of the next — so the previous row's clock_out has to be brought onto the current row. LAG(clock_out) does exactly that, partitioned by associate so one person's shift is never compared with another's, and ordered by clock_in so "previous" means previous in time. The ids are deliberately out of time order, so ordering the window by shift_id would pair the wrong shifts.

TIMESTAMPDIFF(MINUTE, prev_out, clock_in) measures the rest across midnight. Each associate's first shift has no predecessor; its prev_out is NULL and it is filtered out. The threshold is strict — exactly ten hours of rest is acceptable.

The alternative finds the previous end with a correlated subquery: the latest clock_out among the associate's shifts that started earlier. That is the previous shift's end only because shifts never overlap — with overlaps the two queries would disagree. The window version is one sort per associate; the subquery version is a probe per row, fine with an index on (associate_id, clock_in).

Reference solution (MySQL)

WITH s AS (
  SELECT associate_id, shift_id, clock_in,
         LAG(clock_out) OVER (PARTITION BY associate_id ORDER BY clock_in) AS prev_out
  FROM AssociateShift
)
SELECT associate_id, shift_id, TIMESTAMPDIFF(MINUTE, prev_out, clock_in) AS rest_minutes
FROM s
WHERE prev_out IS NOT NULL
  AND TIMESTAMPDIFF(MINUTE, prev_out, clock_in) < 600
ORDER BY associate_id, shift_id

Another way

SELECT associate_id, shift_id, rest_minutes FROM (
  SELECT a.associate_id, a.shift_id,
         TIMESTAMPDIFF(MINUTE, (SELECT MAX(p.clock_out) FROM AssociateShift p
                                WHERE p.associate_id = a.associate_id AND p.clock_in < a.clock_in), a.clock_in) AS rest_minutes
  FROM AssociateShift a
) t
WHERE rest_minutes < 600
ORDER BY associate_id, shift_id

← Walmart: Pickup Slot Fill Rate by Hour of Day · Walmart: Each Store's Sales Gap to Its Region's Leader →