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
    The maximum number of overlapping shifts is 3 (from 16:00 to 17:00). The total overlap duration is: - 2 hours (15:00-17:00) between 1st and 2nd shifts - 1 hour (16:00-17:00) between 1st and 3rd shifts - 7 hours (16:00-23:00) between 2nd and 3rd shifts Total: 10 hours = 600 minutes
  • 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
    The maximum number of overlapping shifts is 2. The total overlap duration is 6 hours (11:00-17:00) = 360 minutes.
  • 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;

All Problems

All Solutions