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 and Concurrent Tasks OverviewTasks TimelineEmployee 1001:Task 1: 08:00-09:00Task 2: 08:30-10:30OverlapTask 3: 11:00-12:00Task 7: 13:00-15:30Process EventsAnalysis ResultsDuration Calculation:Task 1: 60 min + Task 2: 120 minTask 3: 60 min + Task 7: 150 minMinus overlap: 30 minTotal: 360 min = 6 hoursMax Concurrent: 2 tasksFinal Output Tableemployee_idtotal_hoursmax_concurrent100162100222100321
Understanding the Visualization
1
Input Tasks
Tasks with time intervals per employee
2
Event Processing
Convert to events and merge overlaps
3
Results
Total hours and max concurrent tasks
Key Takeaway
🎯 Key Insight: Use event-based processing with CTEs to handle complex time interval overlaps and concurrent task tracking efficiently

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