Employee Work Coverage and Peak Load

Hard

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 1

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 2

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 3

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.

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

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 |

[ {"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} ]

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 |

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 |