Walmart: Associate Shifts Clocked Over Ten Hours
A store's people lead at Walmart reviews long shifts every week, because a shift over ten hours needs a manager's sign-off.
- Difficulty: Easy
- Topics: Dates, Basics
- Dialect: MySQL
- Problem: #1002
Problem statement
A store's people lead at Walmart reviews long shifts every week, because a shift over ten hours needs a manager's sign-off.
For every completed shift (one with a clock_out), work out the minutes between clock_in and clock_out. Return the shifts that ran more than 600 minutes — exactly ten hours is fine — with shift_id, associate_id and minutes_worked. Shifts can run past midnight. Order by minutes_worked descending, then shift_id ascending.
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: TimeClockShift
| Column | Type |
|---|---|
| shift_id | int |
| associate_id | int |
| store_id | int |
| clock_in | datetime |
| clock_out | datetime |
Primary key: shift_id.
One row per shift punched at the time clock. clock_out is NULL while the associate is still on the clock.
Examples
Example 1
TimeClockShift
| shift_id | associate_id | store_id | clock_in | clock_out |
|---|---|---|---|---|
| 1 | 41 | 1204 | 2025-05-05 08:00:00 | 2025-05-05 18:30:00 |
| 2 | 42 | 1204 | 2025-05-05 22:00:00 | 2025-05-06 08:30:00 |
| 3 | 43 | 1204 | 2025-05-06 07:00:00 | 2025-05-06 17:00:00 |
| 4 | 41 | 1204 | 2025-05-07 06:00:00 | NULL |
| 5 | 44 | 3318 | 2025-05-06 06:00:00 | 2025-05-06 12:00:00 |
| 6 | 45 | 3318 | 2025-05-06 09:15:00 | 2025-05-06 20:00:00 |
Output
| shift_id | associate_id | minutes_worked |
|---|---|---|
| 6 | 45 | 645 |
| 1 | 41 | 630 |
| 2 | 42 | 630 |
How to solve Walmart: Associate Shifts Clocked Over Ten Hours
The length of a shift is the distance between two full datetimes, so subtracting clock times alone would break for the overnight shift that starts at 22:00 and ends at 08:30 the next day. TIMESTAMPDIFF(MINUTE, clock_in, clock_out) compares the complete timestamps, so it handles crossing midnight without any special case.
Open shifts have a NULL clock_out. The difference is then NULL and the comparison > 600 is unknown, so the row is filtered out even without the explicit clock_out IS NOT NULL; spelling it out keeps the intent readable. The threshold is strict: a shift of exactly 600 minutes needs no sign-off.
Sorting by the computed minutes and then by shift_id makes the order total, so two shifts of the same length always come out the same way. The alternative measures seconds and integer-divides by 60, filtering in an outer query on the alias. Both are a single pass over the table.
Reference solution (MySQL)
SELECT shift_id, associate_id, TIMESTAMPDIFF(MINUTE, clock_in, clock_out) AS minutes_worked
FROM TimeClockShift
WHERE clock_out IS NOT NULL
AND TIMESTAMPDIFF(MINUTE, clock_in, clock_out) > 600
ORDER BY minutes_worked DESC, shift_idAnother way
SELECT shift_id, associate_id, minutes_worked
FROM (SELECT shift_id, associate_id, TIMESTAMPDIFF(SECOND, clock_in, clock_out) DIV 60 AS minutes_worked FROM TimeClockShift) t
WHERE minutes_worked > 600
ORDER BY minutes_worked DESC, shift_id← Walmart: Grocery vs General Merchandise Sales per Store · Walmart: Supercenters per State From Store Codes →