Employee Task Duration and Concurrent Tasks - Problem

Given a table Tasks with task information including task ID, employee ID, and start/end times, write a SQL query to find:

  • Total duration of tasks for each employee (rounded down to the nearest full hour)
  • Maximum number of concurrent tasks an employee handled at any point in time

The result should be ordered by employee_id in ascending order.

Note: When calculating total duration, overlapping time periods should only be counted once (no double counting).

Table Schema

Tasks
Column Name Type Description
task_id PK int Unique identifier for each task
employee_id PK int Identifier for the employee assigned to the task
start_time datetime Start time of the task
end_time datetime End time of the task
Primary Key: task_id, employee_id
Note: Each row represents a task assigned to an employee with its time duration

Input & Output

Example 1 — Multiple Employees with Overlapping Tasks
Input 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
💡 Note:

Employee 1001: Tasks 1 and 2 overlap from 08:30-09:00 (30 minutes). Total time: 60 + 120 + 60 + 150 - 30 = 360 minutes = 6 hours. Max concurrent: 2 tasks.

Employee 1002: Tasks 4 and 5 overlap from 09:30-10:00 (30 minutes). Total time: 60 + 120 - 30 = 150 minutes = 2.5 hours → 2 hours (rounded down). Max concurrent: 2 tasks.

Employee 1003: Single task with no overlaps. Total time: 120 minutes = 2 hours. Max concurrent: 1 task.
Example 2 — No Overlapping Tasks
Input 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 10:00:00 2023-05-01 11:00:00
3 1002 2023-05-01 09:00:00 2023-05-01 10:30:00
Output:
employee_id total_task_hours max_concurrent_tasks
1001 2 1
1002 1 1
💡 Note:

All tasks are non-overlapping. Employee 1001 has 2 tasks of 1 hour each = 2 total hours. Employee 1002 has 1 task of 1.5 hours → 1 hour (rounded down). Maximum concurrent tasks is 1 for both employees.

Constraints

  • 1 ≤ task_id ≤ 10^5
  • 1 ≤ employee_id ≤ 10^4
  • start_time < end_time
  • start_time and end_time are valid datetime values

Visualization

Tap to expand
Employee Task Duration & Concurrent Tasks INPUT: Tasks Table ID EmpID Start End 1 101 09:00 11:30 2 101 10:00 12:00 3 101 14:00 15:30 4 102 08:00 10:00 5 102 09:30 11:30 Timeline View (Emp 101) 09:00 12:00 15:30 Task 1 Task 2 Task 3 Overlap region Max Concurrent: 2 tasks (10:00-11:30) ALGORITHM STEPS 1 Create Event Points Mark +1 at start, -1 at end for each task time 2 Sort Events by Time Process events in order Track running count 3 Merge Overlaps Use interval merging Avoid double counting 4 Calculate Results Sum merged durations Floor to full hours Running Count Example 09:00 +1 --> count=1 10:00 +1 --> count=2 (MAX) 11:30 -1 --> count=1 12:00 -1 --> count=0 FINAL RESULT emp_id hours max 101 4 2 102 3 2 Employee 101 Calculation Merged: 09:00-12:00 (3h) + 14:00-15:30 (1.5h) Total: 4.5h --> FLOOR = 4h Employee 102 Calculation Merged: 08:00-11:30 (3.5h) Total: 3.5h --> FLOOR = 3h Max concurrent at 09:30-10:00 OK - Ordered by employee_id ASC Key Insight: Use the "sweep line" algorithm with event points (+1 at start, -1 at end) to track concurrent tasks. For duration calculation, merge overlapping intervals first to avoid counting the same time twice. The maximum running count at any point gives the max concurrent tasks. Time complexity: O(n log n) TutorialsPoint - Employee Task Duration and Concurrent Tasks | Optimal Solution

Related Problems

Asked in
Amazon 28 Google 22 Microsoft 18 Meta 15
23.4K Views
Medium Frequency
~25 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen