Walmart: Associate Shifts Clocked Over Ten Hours

Easy
1002.

Walmart: Associate Shifts Clocked Over Ten Hours

EasyDatesBasics

MySQL

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

ColumnType
shift_idint
associate_idint
store_idint
clock_indatetime
clock_outdatetime

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.

Example

The rows must come back in the order shown.

TimeClockShift

shift_idassociate_idstore_idclock_inclock_out
14112042025-05-05 08:00:002025-05-05 18:30:00
24212042025-05-05 22:00:002025-05-06 08:30:00
34312042025-05-06 07:00:002025-05-06 17:00:00
44112042025-05-07 06:00:00NULL
54433182025-05-06 06:00:002025-05-06 12:00:00
64533182025-05-06 09:15:002025-05-06 20:00:00

Output

shift_idassociate_idminutes_worked
645645
141630
242630
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: 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

ColumnType
shift_idint
associate_idint
store_idint
clock_indatetime
clock_outdatetime

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_idassociate_idstore_idclock_inclock_out
14112042025-05-05 08:00:002025-05-05 18:30:00
24212042025-05-05 22:00:002025-05-06 08:30:00
34312042025-05-06 07:00:002025-05-06 17:00:00
44112042025-05-07 06:00:00NULL
54433182025-05-06 06:00:002025-05-06 12:00:00
64533182025-05-06 09:15:002025-05-06 20:00:00

Output

shift_idassociate_idminutes_worked
645645
141630
242630

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_id

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