Table: EmployeeShifts
+------------------+----------+ | Column Name | Type | +------------------+----------+ | employee_id | int | | start_time | datetime | | end_time | datetime | +------------------+----------+ (employee_id, start_time) is the unique key for this table. This table contains information about the shifts worked by employees, including the start time, and end time.
Write a solution to analyze overlapping shifts for each employee. Two shifts are considered overlapping if they occur on the same date and one shift's end_time is later than another shift's start_time.
For each employee, calculate the following:
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 | 2023-10-01 09:00:00 | 2023-10-01 17:00:00 | | 1 | 2023-10-01 15:00:00 | 2023-10-01 23:00:00 | | 1 | 2023-10-01 16:00:00 | 2023-10-02 00:00:00 | | 2 | 2023-10-01 09:00:00 | 2023-10-01 17:00:00 | | 2 | 2023-10-01 11:00:00 | 2023-10-01 19:00:00 | | 3 | 2023-10-01 09:00:00 | 2023-10-01 17:00:00 | +-------------+---------------------+---------------------+
Output:
+-------------+---------------------------+------------------------+ | employee_id | max_overlapping_shifts | total_overlap_duration | +-------------+---------------------------+------------------------+ | 1 | 3 | 600 | | 2 | 2 | 360 | | 3 | 1 | 0 | +-------------+---------------------------+------------------------+
Explanation:
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.
Loading editor...
{"headers": {"EmployeeShifts":["employee_id","start_time","end_time"]},"rows":{"EmployeeShifts":[[1,"2023-10-01 09:00:00","2023-10-01 17:00:00"],[1,"2023-10-01 15:00:00","2023-10-01 23:00:00"],[1,"2023-10-01 16:00:00","2023-10-02 00:00:00"],[2,"2023-10-01 09:00:00","2023-10-01 17:00:00"],[2,"2023-10-01 11:00:00","2023-10-01 19:00:00"],[3,"2023-10-01 09:00:00","2023-10-01 17:00:00"]]}}