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.
We first use a self-join to connect the EmployeeShifts table to itself. The join condition ensures that we only compare shifts belonging to the same employee and check if there is any overlap between shifts.
t1.start_time < t2.start_time: Ensures that the start time of the first shift is earlier than the start time of the second shift.t1.end_time > t2.start_time: Ensures that the end time of the first shift is later than the start time of the second shift.Next, we group the data by employee_id and count the number of overlapping shifts for each employee.
Finally, we filter out employees with overlapping shift counts greater than 0 and sort the results in ascending order by employee_id.
Pandas
LeetCode was HARD until I Learned these 15 Patterns • Ashish Pratap Singh • 1,002,296 views views
Watch 9 more video solutions →Practice Find Overlapping Shifts with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor