Welcome to Subscribe On Youtube
3268. Find Overlapping Shifts II 🔒
Description
Table: EmployeeShifts
++-+ \| employee_id \| int \| \| start_time \| datetime \| \| end_time \| datetime \| +++++++ \| employee_id \| max_overlapping_shifts \| total_overlap_duration \| +-+++
Explanation:
- Employee 1 has 3 shifts:
- 2023-10-01 09:00:00 to 2023-10-01 17:00:00
- 2023-10-01 15:00:00 to 2023-10-01 23:00:00
- 2023-10-01 16:00:00 to 2023-10-02 00:00:00
- Employee 2 has 2 shifts:
- 2023-10-01 09:00:00 to 2023-10-01 17:00:00
- 2023-10-01 11:00:00 to 2023-10-01 19:00:00
- Employee 3 has only 1 shift, so there are no overlaps.
The output table contains the employee_id, the maximum number of simultaneous overlaps, and the total overlap duration in minutes for each employee, ordered by employee_id in ascending order.
</div>
Solutions
Solution 1: Merge + Join
We can merge all the start_time and end_time for each employee_id and store them in table T. Then, by using the LEAD function, we calculate the next time period for each employee_id and store it in table P.
Next, we can join table P with the EmployeeShifts table to calculate the concurrent_count for each employee_id, which represents the number of overlapping time periods. This is stored in table S.
Finally, we can perform a self-join on the EmployeeShifts table to calculate the total_overlap_duration for each employee_id, representing the total overlapping time, and store it in table U.
Ultimately, we can join tables S and U to calculate the max_overlapping_shifts and total_overlap_duration for each employee_id.
Similar Problems:
-
WITH T AS ( SELECT DISTINCT employee_id, start_time AS st FROM EmployeeShifts UNION DISTINCT SELECT DISTINCT employee_id, end_time AS st FROM EmployeeShifts ), P AS ( SELECT *, LEAD(st) OVER ( PARTITION BY employee_id ORDER BY st ) AS ed FROM T ), S AS ( SELECT P.*, COUNT(1) AS concurrent_count FROM P INNER JOIN EmployeeShifts USING (employee_id) WHERE P.st >= EmployeeShifts.start_time AND P.ed <= EmployeeShifts.end_time GROUP BY 1, 2, 3 ), U AS ( SELECT t1.employee_id, SUM( TIMESTAMPDIFF(MINUTE, t2.start_time, LEAST(t1.end_time, t2.end_time)) ) total_overlap_duration FROM EmployeeShifts t1 JOIN EmployeeShifts t2 ON t1.employee_id = t2.employee_id AND t1.start_time < t2.start_time AND t1.end_time > t2.start_time GROUP BY 1 ) SELECT employee_id, MAX(concurrent_count) max_overlapping_shifts, IFNULL(AVG(total_overlap_duration), 0) total_overlap_duration FROM S LEFT JOIN U USING (employee_id) GROUP BY 1 ORDER BY 1;