Find Total Time Spent by Each Employee - Practice Coding Problems

Find Total Time Spent by Each Employee - Problem

Database Easy

Employee Time Tracker - Office Hours Calculator

You're building a time tracking system for a company that monitors when employees enter and leave the office. The system records multiple entries and exits throughout the day for each employee.

📊 Your Task:
Calculate the total time spent by each employee in the office for each day. An employee might have multiple entry-exit sessions on the same day (like leaving for lunch and coming back).

🏢 The Database Schema:

Column Name	Type	Description
`emp_id`	int	Employee identifier
`event_day`	date	Date of the office visit
`in_time`	int	Entry time in minutes (1-1440)
`out_time`	int	Exit time in minutes (1-1440)

🎯 Goal: Return each employee's total office hours per day by summing up all their (out_time - in_time) sessions.

Input & Output

example_1.sql — Basic Case

$ Input: Employees table: +--------+------------+---------+----------+ | emp_id | event_day | in_time | out_time | +--------+------------+---------+----------+ | 1 | 2020-11-28 | 4 | 32 | | 1 | 2020-11-28 | 55 | 200 | | 1 | 2020-12-03 | 1 | 42 | | 2 | 2020-11-28 | 3 | 33 | | 2 | 2020-12-09 | 47 | 74 | +--------+------------+---------+----------+

› Output: +--------+------------+------------+ | emp_id | day | total_time | +--------+------------+------------+ | 1 | 2020-11-28 | 173 | | 1 | 2020-12-03 | 41 | | 2 | 2020-11-28 | 30 | | 2 | 2020-12-09 | 27 | +--------+------------+------------+

💡 Note: Employee 1 worked two sessions on 2020-11-28: (32-4) + (200-55) = 28 + 145 = 173 minutes. Employee 2 worked single sessions on both days: (33-3) = 30 minutes and (74-47) = 27 minutes.

example_2.sql — Single Sessions

$ Input: Employees table: +--------+------------+---------+----------+ | emp_id | event_day | in_time | out_time | +--------+------------+---------+----------+ | 3 | 2020-11-30 | 60 | 120 | | 4 | 2020-11-30 | 90 | 150 | +--------+------------+---------+----------+

› Output: +--------+------------+------------+ | emp_id | day | total_time | +--------+------------+------------+ | 3 | 2020-11-30 | 60 | | 4 | 2020-11-30 | 60 | +--------+------------+------------+

💡 Note: Both employees worked exactly 60 minutes each: (120-60) = 60 and (150-90) = 60.

example_3.sql — Multiple Days

$ Input: Employees table: +--------+------------+---------+----------+ | emp_id | event_day | in_time | out_time | +--------+------------+---------+----------+ | 5 | 2020-11-28 | 10 | 50 | | 5 | 2020-11-29 | 20 | 80 | | 5 | 2020-11-29 | 100 | 140 | +--------+------------+---------+----------+

› Output: +--------+------------+------------+ | emp_id | day | total_time | +--------+------------+------------+ | 5 | 2020-11-28 | 40 | | 5 | 2020-11-29 | 100 | +--------+------------+------------+

💡 Note: Employee 5 worked 40 minutes on 2020-11-28 and 100 minutes total on 2020-11-29 from two sessions: (80-20) + (140-100) = 60 + 40 = 100 minutes.

Visualization

Tap to expand

Understanding the Visualization

Raw Badge Swipes

Every entry/exit creates a record with timestamp

Group by Worker & Day

Combine all sessions for same person on same day

Calculate Session Times

Find duration of each individual session

Sum Daily Totals

Add up all session times for final daily hours

Key Takeaway

🎯 Key Insight: Use SQL GROUP BY to aggregate time sessions per employee per day, then SUM the time differences to get total daily hours.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through all records with grouping

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of unique (employee, day) combinations

✓ Linear Space

Constraints

1 ≤ Number of employees ≤ 100
1 ≤ in_time < out_time ≤ 1440
Each (emp_id, event_day, in_time) combination is unique
Time values represent minutes from midnight (1440 = 24 hours)
No overlapping sessions for the same employee on the same day

Asked in

a Amazon 25 ⊞ Microsoft 18 G Google 15 f Meta 12

This is a classic SQL aggregation problem solved with GROUP BY and SUM. The key insight is to group records by (employee_id, date) and sum up the time differences (out_time - in_time) for each group. This handles multiple entry/exit sessions per employee per day automatically.

Common Approaches

Approach	Time	Space	Notes
✓ Basic SQL Aggregation	O(n)	O(k)	Direct GROUP BY with SUM aggregation

Basic SQL Aggregation — Algorithm Steps

Group all records by emp_id and event_day
For each group, calculate (out_time - in_time) for each record
Sum up all the time differences within each group
Return the results with proper column names

Visualization

Tap to expand

Step-by-Step Walkthrough

Input Records

Raw employee entry/exit records

Group by Employee + Day

Combine records with same employee ID and date

Calculate Time Differences

Compute (out_time - in_time) for each session

Sum Within Groups

Add up all time spent for each employee-day combination

Code -

solution.c — C

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

typedef struct {
    int emp_id;
    char event_day[12];
    int in_time;
    int out_time;
} EmployeeRecord;

typedef struct {
    int emp_id;
    char day[12];
    int total_time;
} Result;

int solution(EmployeeRecord* employees, int n, Result* results) {
    int resultCount = 0;
    
    for (int i = 0; i < n; i++) {
        int timeSpent = employees[i].out_time - employees[i].in_time;
        int found = 0;
        
        // Look for existing group
        for (int j = 0; j < resultCount; j++) {
            if (results[j].emp_id == employees[i].emp_id && 
                strcmp(results[j].day, employees[i].event_day) == 0) {
                results[j].total_time += timeSpent;
                found = 1;
                break;
            }
        }
        
        // Create new group if not found
        if (!found) {
            results[resultCount].emp_id = employees[i].emp_id;
            strcpy(results[resultCount].day, employees[i].event_day);
            results[resultCount].total_time = timeSpent;
            resultCount++;
        }
    }
    
    return resultCount;
}

int main() {
    // Parse input and call solution
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through all records with grouping

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of unique (employee, day) combinations

✓ Linear Space

Constraints

1 ≤ Number of employees ≤ 100
1 ≤ in_time < out_time ≤ 1440
Each (emp_id, event_day, in_time) combination is unique
Time values represent minutes from midnight (1440 = 24 hours)
No overlapping sessions for the same employee on the same day

28.4K Views

Medium Frequency

~8 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Basic SQL Aggregation — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler