You are given a table EmployeeShifts that contains information about employee work shifts with start and end times.
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 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.
Table Schema
| Column Name | Type | Description |
|---|---|---|
employee_id
PK
|
int | Employee identifier |
start_time
PK
|
datetime | Shift start timestamp |
end_time
|
datetime | Shift end timestamp |
Input & Output
| 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 |
| employee_id | max_overlapping_shifts | total_overlap_duration |
|---|---|---|
| 1 | 3 | 600 |
| 2 | 2 | 360 |
| 3 | 1 | 0 |
Employee 1 has 3 overlapping shifts with maximum 3 simultaneous shifts from 16:00-17:00. Total overlaps: 2hrs (15:00-17:00) + 1hr (16:00-17:00) + 7hrs (16:00-23:00) = 600 minutes. Employee 2 has 2 shifts overlapping 6 hours (11:00-17:00) = 360 minutes. Employee 3 has only 1 shift, so no overlaps.
| employee_id | start_time | end_time |
|---|---|---|
| 1 | 2023-10-01 09:00:00 | 2023-10-01 12:00:00 |
| 1 | 2023-10-01 13:00:00 | 2023-10-01 17:00:00 |
| 2 | 2023-10-01 08:00:00 | 2023-10-01 16:00:00 |
| employee_id | max_overlapping_shifts | total_overlap_duration |
|---|---|---|
| 1 | 1 | 0 |
| 2 | 1 | 0 |
Employee 1 has two consecutive shifts with no overlap (12:00-13:00 gap). Employee 2 has only one shift. Both have max_overlapping_shifts of 1 and total_overlap_duration of 0.
Constraints
-
1 ≤ employee_id ≤ 1000 -
start_time < end_timefor all shifts - All timestamps are valid datetime values