3268. Find Overlapping Shifts II π
Description
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:
- The maximum number of shifts that overlap at any given time.
- The total duration of all overlaps in minutes.
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:
- 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.
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
|