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

Loading problem...

Preparing your editor. You can read the problem while it loads.

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