| comments | difficulty | edit_url | tags | |
|---|---|---|---|---|
true |
Hard |
|
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.
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;