Consecutive Shifts Over 12 Hours | Dataford Interview Questions

Write a query to identify employees who have worked consecutive shifts exceeding twelve hours.

Treat two shifts as consecutive when the later shift starts no more than 30 minutes after the earlier shift ends. Return each qualifying pair once, excluding incomplete shifts. Sort the results by employee ID and the first shift's start time.

Output

One row per qualifying consecutive shift pair
Columns: employee_id, employee_name, first_shift_id, first_shift_start, first_shift_end, next_shift_start, next_shift_end, and consecutive_hours
Include only pairs spanning more than 12 hours from the first shift start to the next shift end
Order by employee_id, then first_shift_start

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
department	VARCHAR(80)	Employee's department

shifts

Column	Type	Description
shift_idPK	INT	Unique shift identifier
employee_id	INT	Employee assigned to the shift
shift_start	TIMESTAMP	Shift start timestamp
shift_end	TIMESTAMP	Shift end timestamp

Your question is Consecutive Shifts Over 12 Hours. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem