Employee Work Coverage and Peak Load

HARD30 pts

An operations analytics team needs two workforce metrics per employee from raw task intervals.

Table: Tasks

task_id: task identifier
employee_id: employee identifier
start_time: task start timestamp (YYYY-MM-DD HH:MM:SS)
end_time: task end timestamp (YYYY-MM-DD HH:MM:SS)

Each row represents one task interval handled by an employee.

Your objective: For each employee_id, compute:

total_task_hours
- First compute busy time as the UNION length of all that employee's task intervals.
- Overlaps must be counted once.
- Convert busy seconds to full hours by floor division.
max_concurrent_tasks
- Maximum number of active tasks that employee handled at any instant.

Important interval rule:

Treat intervals as half-open: [start_time, end_time)
If one task ends exactly when another starts, they are not overlapping.

Output requirements:

Return exactly these columns in this order: employee_id, total_task_hours, max_concurrent_tasks
Return rows sorted by employee_id ascending.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Tasks:
| task_id | employee_id | start_time           | end_time             |
|---------|-------------|----------------------|----------------------|
| 1       | 1001        | 2024-05-01 08:00:00  | 2024-05-01 09:00:00  |
| 2       | 1001        | 2024-05-01 08:30:00  | 2024-05-01 10:30:00  |
| 3       | 1001        | 2024-05-01 11:00:00  | 2024-05-01 12:00:00  |
| 4       | 1001        | 2024-05-01 13:00:00  | 2024-05-01 15:30:00  |
| 5       | 1002        | 2024-05-01 09:00:00  | 2024-05-01 10:00:00  |
| 6       | 1002        | 2024-05-01 09:30:00  | 2024-05-01 11:30:00  |
| 7       | 1003        | 2024-05-01 14:00:00  | 2024-05-01 16:00:00  |

Output

[
  {"employee_id":1001,"total_task_hours":6,"max_concurrent_tasks":2},
  {"employee_id":1002,"total_task_hours":2,"max_concurrent_tasks":2},
  {"employee_id":1003,"total_task_hours":2,"max_concurrent_tasks":1}
]

Explanation

Employee 1001 has 6 busy hours after overlap deduplication and reaches concurrency 2. Employee 1002 has 2.5 busy hours (floors to 2) with peak concurrency 2. Employee 1003 has a single 2-hour task.

Example

Input

Tasks:
| task_id | employee_id | start_time           | end_time             |
|---------|-------------|----------------------|----------------------|
| 101     | 2001        | 2024-05-02 08:00:00  | 2024-05-02 09:00:00  |
| 102     | 2001        | 2024-05-02 09:00:00  | 2024-05-02 10:00:00  |
| 103     | 2001        | 2024-05-02 10:00:00  | 2024-05-02 10:30:00  |
| 104     | 2002        | 2024-05-02 12:00:00  | 2024-05-02 13:00:00  |
| 105     | 2002        | 2024-05-02 12:15:00  | 2024-05-02 12:45:00  |
| 106     | 2002        | 2024-05-02 12:30:00  | 2024-05-02 13:30:00  |

Output

[
  {"employee_id":2001,"total_task_hours":2,"max_concurrent_tasks":1},
  {"employee_id":2002,"total_task_hours":1,"max_concurrent_tasks":3}
]

Explanation

Back-to-back intervals for employee 2001 do not overlap under half-open semantics. Employee 2002 reaches a 3-task overlap window from 12:30 to 12:45.

Example

Input

Tasks:
| task_id | employee_id | start_time           | end_time             |
|---------|-------------|----------------------|----------------------|
| 201     | 3001        | 2024-05-03 00:00:00  | 2024-05-03 08:00:00  |
| 202     | 3001        | 2024-05-03 01:00:00  | 2024-05-03 07:00:00  |
| 203     | 3001        | 2024-05-03 02:00:00  | 2024-05-03 06:00:00  |
| 204     | 3001        | 2024-05-03 03:00:00  | 2024-05-03 05:00:00  |
| 205     | 3002        | 2024-05-03 09:00:00  | 2024-05-03 09:59:00  |

Output

[
  {"employee_id":3001,"total_task_hours":8,"max_concurrent_tasks":4},
  {"employee_id":3002,"total_task_hours":0,"max_concurrent_tasks":1}
]

Explanation

For employee 3001, nested intervals produce 8 busy hours and peak concurrency 4. Employee 3002 has only 59 minutes of busy time, which floors to 0 hours.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in Tasks <= 300000
1 <= task_id <= 2000000000
1 <= employee_id <= 2000000000
(task_id, employee_id) is unique
start_time and end_time use format YYYY-MM-DD HH:MM:SS
For every row: start_time < end_time
Intervals are evaluated as half-open [start_time, end_time)
Return exactly columns: employee_id, total_task_hours, max_concurrent_tasks
Sort output by employee_id ascending
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with exact required columns

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Tasks =

[{"task_id":1,"employee_id":1001,"start_time":"2024-05-01 08:00:00","end_time":"2024-05-01 09:00:00"},{"task_id":5,"employee_id":1002,"start_time":"2024-05-01 09:00:00","end_time":"2024-05-01 10:00:00"},{"task_id":4,"employee_id":1001,"start_time":"2024-05-01 13:00:00","end_time":"2024-05-01 15:30:00"},{"task_id":7,"employee_id":1003,"start_time":"2024-05-01 14:00:00","end_time":"2024-05-01 16:00:00"},{"task_id":3,"employee_id":1001,"start_time":"2024-05-01 11:00:00","end_time":"2024-05-01 12:00:00"},{"task_id":2,"employee_id":1001,"start_time":"2024-05-01 08:30:00","end_time":"2024-05-01 10:30:00"},{"task_id":6,"employee_id":1002,"start_time":"2024-05-01 09:30:00","end_time":"2024-05-01 11:30:00"}]

Employee Work Coverage and Peak Load

Hints

Employee Work Coverage and Peak Load

Hints