Watch 10 video solutions for Find Overlapping Shifts, a medium level problem involving Database. This walkthrough by Ashish Pratap Singh has 1,002,296 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: EmployeeShifts
+------------------+---------+ | Column Name | Type | +------------------+---------+ | employee_id | int | | start_time | time | | end_time | time | +------------------+---------+ (employee_id, start_time) is the unique key for this table. This table contains information about the shifts worked by employees, including the start and end times on a specific date.
Write a solution to count the number of overlapping shifts for each employee. Two shifts are considered overlapping if one shift’s end_time is later than another shift’s start_time.
Return the result table ordered by employee_id in ascending order.
The query result format is in the following example.
Example:
Input:
EmployeeShifts table:
+-------------+------------+----------+ | employee_id | start_time | end_time | +-------------+------------+----------+ | 1 | 08:00:00 | 12:00:00 | | 1 | 11:00:00 | 15:00:00 | | 1 | 14:00:00 | 18:00:00 | | 2 | 09:00:00 | 17:00:00 | | 2 | 16:00:00 | 20:00:00 | | 3 | 10:00:00 | 12:00:00 | | 3 | 13:00:00 | 15:00:00 | | 3 | 16:00:00 | 18:00:00 | | 4 | 08:00:00 | 10:00:00 | | 4 | 09:00:00 | 11:00:00 | +-------------+------------+----------+
Output:
+-------------+--------------------+ | employee_id | overlapping_shifts | +-------------+--------------------+ | 1 | 2 | | 2 | 1 | | 4 | 1 | +-------------+--------------------+
Explanation:
The output shows the employee_id and the count of overlapping shifts for each employee who has at least one overlapping shift, ordered by employee_id in ascending order.
