Watch 10 video solutions for Employee Task Duration and Concurrent Tasks, a hard level problem involving Database. This walkthrough by ThePrimeTime has 1,196,262 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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:
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:
Note: Output table is ordered by employee_id in ascending order.
