Employee Task Duration and Concurrent Tasks

Employee Task Duration and Concurrent Tasks - Problem

Database Hard

You're managing a company's task tracking system and need to analyze employee productivity. Given a database table of employee tasks with start and end times, your goal is to calculate two key metrics for each employee:

Total Task Hours: The sum of all task durations (excluding overlapping time) rounded down to the nearest full hour
Maximum Concurrent Tasks: The highest number of tasks an employee was working on simultaneously at any point

The challenge lies in properly handling overlapping tasks. When an employee works on multiple tasks simultaneously, you should only count the overlapped time once in the total duration calculation.

Example: If Task A runs from 9:00-11:00 (2 hours) and Task B runs from 10:00-12:00 (2 hours), the total working time is 3 hours (not 4), because the hour from 10:00-11:00 overlaps.

Return results ordered by employee_id in ascending order.

Input & Output

basic_example.sql — Basic overlapping tasks

$ Input: Tasks: [(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')]

› Output: [(1001, 2, 2)]

💡 Note: Employee 1001 has two overlapping tasks. Task 1 (60 min) + Task 2 (120 min) - overlap (30 min) = 150 min = 2.5 hours → 2 hours. Maximum concurrent tasks is 2 during the overlap period.

multiple_employees.sql — Multiple employees with different patterns

$ Input: Tasks: [(1,1001,'08:00','09:00'), (2,1001,'08:30','10:30'), (3,1002,'09:00','10:00'), (4,1002,'11:00','12:00')]

› Output: [(1001, 2, 2), (1002, 2, 1)]

💡 Note: Employee 1001: overlapping tasks total 2.5h→2h, max concurrent 2. Employee 1002: non-overlapping tasks total 2h, max concurrent 1.

edge_case.sql — Single task and complex overlaps

$ Input: Tasks: [(1,1001,'08:00','10:00'), (2,1001,'09:00','11:00'), (3,1001,'10:30','12:00'), (4,1002,'14:00','15:00')]

› Output: [(1001, 4, 2), (1002, 1, 1)]

💡 Note: Employee 1001: Three tasks with partial overlaps create total coverage from 08:00-12:00 (4 hours). Max concurrent is 2. Employee 1002: Single 1-hour task.

Constraints

1 ≤ number of tasks ≤ 10⁴
1 ≤ employee_id ≤ 10⁹
start_time < end_time for all tasks
All datetime values are valid
Task duration is at least 1 minute

Visualization

Tap to expand

Understanding the Visualization

Timeline Events

Mark when each task starts and ends, like noting when dishes go on/off the stove

Chronological Processing

Walk through time, updating how many tasks are active at each moment

Active Time Tracking

Sum up periods when at least one task was running

Peak Activity

Record the maximum number of simultaneous tasks

Key Takeaway

🎯 Key Insight: Transform the overlap problem into a chronological event stream. This allows us to efficiently track both total active time and peak concurrency in a single pass through sorted events.

Asked in

G Google 45 a Amazon 38 ⊞ Microsoft 32 f Meta 28

The optimal solution uses a sweep line algorithm with event processing. Convert each task into start/end events, sort them chronologically, then process in order to track active periods and maximum concurrency. This approach efficiently handles complex overlapping scenarios in O(n log n) time.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Interval Overlap Checking)	O(n³)	O(n)	Check every pair of tasks for overlaps and manually calculate merged intervals
Event-Based Sweep Line (Optimal)	O(n log n)	O(n)	Use sweep line algorithm with start/end events to efficiently track overlaps and concurrency

Brute Force (Interval Overlap Checking) — Algorithm Steps

Group tasks by employee_id
For each employee, check all pairs of tasks for overlaps
Merge overlapping intervals manually
Sum durations of merged intervals for total time
Check concurrency at every minute to find maximum

Visualization

Tap to expand

Step-by-Step Walkthrough

Group by Employee

Separate tasks by employee_id

Check All Pairs

For each employee, compare every task with every other task

Calculate Overlaps

Manually compute intersection periods

Sum Durations

Add individual durations and subtract overlaps

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include <math.h>

typedef struct {
    int task_id;
    int employee_id;
    time_t start_time;
    time_t end_time;
} Task;

typedef struct {
    int employee_id;
    int total_task_hours;
    int max_concurrent_tasks;
} Result;

int compareResults(const void *a, const void *b) {
    return ((Result*)a)->employee_id - ((Result*)b)->employee_id;
}

int analyzeEmployeeTasks(Task* tasks, int taskCount, Result* results) {
    // Simple brute force implementation
    int employeeIds[1000];
    int uniqueEmployees = 0;
    
    // Find unique employees
    for (int i = 0; i < taskCount; i++) {
        int found = 0;
        for (int j = 0; j < uniqueEmployees; j++) {
            if (employeeIds[j] == tasks[i].employee_id) {
                found = 1;
                break;
            }
        }
        if (!found) {
            employeeIds[uniqueEmployees++] = tasks[i].employee_id;
        }
    }
    
    // Process each employee
    for (int e = 0; e < uniqueEmployees; e++) {
        int empId = employeeIds[e];
        Task empTasks[100];
        int empTaskCount = 0;
        
        // Collect tasks for this employee
        for (int i = 0; i < taskCount; i++) {
            if (tasks[i].employee_id == empId) {
                empTasks[empTaskCount++] = tasks[i];
            }
        }
        
        // Calculate total minutes and overlaps (brute force)
        long totalMinutes = 0;
        long overlapMinutes = 0;
        
        for (int i = 0; i < empTaskCount; i++) {
            totalMinutes += (empTasks[i].end_time - empTasks[i].start_time) / 60;
        }
        
        // Calculate overlaps
        for (int i = 0; i < empTaskCount; i++) {
            for (int j = i + 1; j < empTaskCount; j++) {
                time_t overlapStart = empTasks[i].start_time > empTasks[j].start_time ? 
                    empTasks[i].start_time : empTasks[j].start_time;
                time_t overlapEnd = empTasks[i].end_time < empTasks[j].end_time ? 
                    empTasks[i].end_time : empTasks[j].end_time;
                
                if (overlapStart < overlapEnd) {
                    overlapMinutes += (overlapEnd - overlapStart) / 60;
                }
            }
        }
        
        // Calculate max concurrent
        int maxConcurrent = 1;
        for (int i = 0; i < empTaskCount; i++) {
            int concurrent = 0;
            for (int j = 0; j < empTaskCount; j++) {
                if (empTasks[i].start_time >= empTasks[j].start_time && 
                    empTasks[i].start_time < empTasks[j].end_time) {
                    concurrent++;
                }
            }
            if (concurrent > maxConcurrent) {
                maxConcurrent = concurrent;
            }
        }
        
        results[e].employee_id = empId;
        results[e].total_task_hours = (int)floor((totalMinutes - overlapMinutes) / 60.0);
        results[e].max_concurrent_tasks = maxConcurrent;
    }
    
    qsort(results, uniqueEmployees, sizeof(Result), compareResults);
    return uniqueEmployees;
}

Time & Space Complexity

Time Complexity

⏱️

O(n³)

O(n²) for pairwise overlap checking plus O(n²) for merging intervals, multiplied by number of employees

⚠ Quadratic Growth

Space Complexity

O(n)

Space for storing merged intervals and temporary calculations

⚡ Linearithmic Space

48.0K Views

High Frequency

~25 min Avg. Time

1.9K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Brute Force (Interval Overlap Checking) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler