Welcome to Subscribe On Youtube

3262. Find Overlapping Shifts 🔒

Description

Table: EmployeeShifts

+++
\| employee_id      \| int     \|
\| start_time       \| time    \|
\| end_time         \| time    \|
++-+
\| employee_id \| start_time \| end_time \|
+-++-+

Output:

+-+--+
\| employee_id \| overlapping_shifts \|
+-+--+
\| 1           \| 2                  \|
\| 2           \| 1                  \|
\| 4           \| 1                  \|
+-+--+

Explanation:

  • Employee 1 has 3 shifts:
    • 08:00:00 to 12:00:00
    • 11:00:00 to 15:00:00
    • 14:00:00 to 18:00:00
    The first shift overlaps with the second, and the second overlaps with the third, resulting in 2 overlapping shifts.
  • Employee 2 has 2 shifts:
    • 09:00:00 to 17:00:00
    • 16:00:00 to 20:00:00
    These shifts overlap with each other, resulting in 1 overlapping shift.
  • Employee 3 has 3 shifts:
    • 10:00:00 to 12:00:00
    • 13:00:00 to 15:00:00
    • 16:00:00 to 18:00:00
    None of these shifts overlap, so Employee 3 is not included in the output.
  • Employee 4 has 2 shifts:
    • 08:00:00 to 10:00:00
    • 09:00:00 to 11:00:00
    These shifts overlap with each other, resulting in 1 overlapping shift.

The output shows the employee_id and the count of overlapping shifts for each employee who has at least one overlapping shift, ordered by employee_id in ascending order.

</div>

Solutions

Solution 1: Self-Join + Group Counting

We first use a self-join to connect the EmployeeShifts table to itself. The join condition ensures that we only compare shifts belonging to the same employee and check if there is any overlap between shifts.

  1. t1.start_time < t2.start_time: Ensures that the start time of the first shift is earlier than the start time of the second shift.
  2. t1.end_time > t2.start_time: Ensures that the end time of the first shift is later than the start time of the second shift.

Next, we group the data by employee_id and count the number of overlapping shifts for each employee.

Finally, we filter out employees with overlapping shift counts greater than $0$ and sort the results in ascending order by employee_id.

  • import pandas as pd


def find_overlapping_shifts(employee_shifts: pd.DataFrame) -> pd.DataFrame:
    merged_shifts = employee_shifts.merge(
        employee_shifts, on="employee_id", suffixes=("_t1", "_t2")
    )
    overlapping_shifts = merged_shifts[
        (merged_shifts["start_time_t1"] < merged_shifts["start_time_t2"])
        & (merged_shifts["end_time_t1"] > merged_shifts["start_time_t2"])
    ]
    result = (
        overlapping_shifts.groupby("employee_id")
        .size()
        .reset_index(name="overlapping_shifts")
    )
    result = result[result["overlapping_shifts"] > 0]
    result = result.sort_values("employee_id").reset_index(drop=True)
    return result


  • SELECT
    t1.employee_id,
    COUNT(*) AS overlapping_shifts
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
HAVING overlapping_shifts > 0
ORDER BY 1;

All Problems

All Solutions