Employee task duration and concurrent tasks

# Table: Tasks
# 
# +---------------+----------+
# | Column Name   | Type     |
# +---------------+----------+
# | task_id       | int      |
# | employee_id   | int      |
# | start_time    | datetime |
# | end_time      | datetime |
# +---------------+----------+
# (task_id, employee_id) is the primary key for this table.
# Each row in this table contains the task identifier, the employee identifier, and the start and end times of each task.
# 
# Write a solution to find the total duration of tasks for each employee and the maximum number of concurrent tasks an employee handled at any point in time. The total duration should be rounded down to the nearest number of full hours.
# 
# Return the result table ordered by employee_id ascending order.
# 
# The result format is in the following example.
#
# Example 1:
# Input:
# Tasks table:
# +---------+-------------+---------------------+---------------------+
# | task_id | employee_id | start_time          | end_time            |
# +---------+-------------+---------------------+---------------------+
# | 1       | 1001        | 2023-05-01 08:00:00 | 2023-05-01 09:00:00 |
# | 2       | 1001        | 2023-05-01 08:30:00 | 2023-05-01 10:30:00 |
# | 3       | 1001        | 2023-05-01 11:00:00 | 2023-05-01 12:00:00 |
# | 7       | 1001        | 2023-05-01 13:00:00 | 2023-05-01 15:30:00 |
# | 4       | 1002        | 2023-05-01 09:00:00 | 2023-05-01 10:00:00 |
# | 5       | 1002        | 2023-05-01 09:30:00 | 2023-05-01 11:30:00 |
# | 6       | 1003        | 2023-05-01 14:00:00 | 2023-05-01 16:00:00 |
# +---------+-------------+---------------------+---------------------+
# Output:
# +-------------+------------------+----------------------+
# | employee_id | total_task_hours | max_concurrent_tasks |
# +-------------+------------------+----------------------+
# | 1001        | 6                | 2                    |
# | 1002        | 2                | 2                    |
# | 1003        | 2                | 1                    |
# +-------------+------------------+----------------------+
# Explanation:
# For employee ID 1001:
# Task 1 and Task 2 overlap from 08:30 to 09:00 (30 minutes).
# Task 7 has a duration of 150 minutes (2 hours and 30 minutes).
# Total task time: 60 (Task 1) + 120 (Task 2) + 60 (Task 3) + 150 (Task 7) - 30 (overlap) = 360 minutes = 6 hours.
# Maximum concurrent tasks: 2 (during the overlap period).
# For employee ID 1002:
# Task 4 and Task 5 overlap from 09:30 to 10:00 (30 minutes).
# Total task time: 60 (Task 4) + 120 (Task 5) - 30 (overlap) = 150 minutes = 2 hours and 30 minutes.
# Total task hours (rounded down): 2 hours.
# Maximum concurrent tasks: 2 (during the overlap period).
# For employee ID 1003:
# No overlapping tasks.
# Total task time: 120 minutes = 2 hours.
# Maximum concurrent tasks: 1.
# Note: Output table is ordered by employee_id in ascending order.

import pandas as pd

def find_total_duration(tasks: pd.DataFrame) -> pd.DataFrame:
  tasks['start_time'] = pd.to_datetime(tasks['start_time'])
  tasks['end_time'] = pd.to_datetime(tasks['end_time'])
  results = []
  for emp_id, group in tasks.groupby('employee_id'):
    intervals = list(zip(group['start_time'], group['end_time']))
    # Max concurrent tasks using sweep line
    events = []
    for s, e in intervals:
      events.append((s, 1))
      events.append((e, -1))
    events.sort()
    max_concurrent = 0
    current = 0
    for _, delta in events:
      current += delta
      max_concurrent = max(max_concurrent, current)
    # Total duration (merge overlapping intervals)
    intervals.sort()
    merged = [intervals[0]]
    for s, e in intervals[1:]:
      if s <= merged[-1][1]:
        merged[-1] = (merged[-1][0], max(merged[-1][1], e))
      else:
        merged.append((s, e))
    total_seconds = sum((e - s).total_seconds() for s, e in merged)
    total_hours = int(total_seconds // 3600)
    results.append({'employee_id': emp_id, 'total_task_hours': total_hours, 'max_concurrent_tasks': max_concurrent})
  return pd.DataFrame(results).sort_values('employee_id').reset_index(drop=True)