Skip to main content
Talentd

Find Total Time Spent by Each Employee - Solution & Explanation

EasyDatabase7 min readAsked at: Amazon, Cognizant, Google
Practice this problem

Problem Statement

Table: Employees

+-------------+------+
| Column Name | Type |
+-------------+------+
| emp_id      | int  |
| event_day   | date |
| in_time     | int  |
| out_time    | int  |
+-------------+------+
(emp_id, event_day, in_time) is the primary key (combinations of columns with unique values) of this table.
The table shows the employees' entries and exits in an office.
event_day is the day at which this event happened, in_time is the minute at which the employee entered the office, and out_time is the minute at which they left the office.
in_time and out_time are between 1 and 1440.
It is guaranteed that no two events on the same day intersect in time, and in_time < out_time.

 

Write a solution to calculate the total time in minutes spent by each employee on each day at the office. Note that within one day, an employee can enter and leave more than once. The time spent in the office for a single entry is out_time - in_time.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

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: 
+------------+--------+------------+
| day        | emp_id | total_time |
+------------+--------+------------+
| 2020-11-28 | 1      | 173        |
| 2020-11-28 | 2      | 30         |
| 2020-12-03 | 1      | 41         |
| 2020-12-09 | 2      | 27         |
+------------+--------+------------+
Explanation: 
Employee 1 has three events: two on day 2020-11-28 with a total of (32 - 4) + (200 - 55) = 173, and one on day 2020-12-03 with a total of (42 - 1) = 41.
Employee 2 has two events: one on day 2020-11-28 with a total of (33 - 3) = 30, and one on day 2020-12-09 with a total of (74 - 47) = 27.

Approach Overview

Problem Overview: Each record stores an employee login interval with in_time and out_time for a specific day. The task is to compute how long each employee worked on that day by summing out_time - in_time across all sessions. The result should return the employee id, the day, and the total time spent.

Approach 1: SQL Aggregation (O(n) time, O(1) extra space)

This problem maps directly to a grouping query in SQL. Each row already represents a session, so the only operation required is computing the session duration and summing it per employee per day. Use SUM(out_time - in_time) and group by emp_id and event_day. The database engine scans the table once and aggregates rows belonging to the same group. This approach is optimal for a database problem because relational engines are built for aggregation operations. It keeps the query simple and executes in linear time relative to the number of rows.

Approach 2: Programmatic Aggregation (Hash Map) (O(n) time, O(n) space)

If the records are processed in an application layer instead of SQL, treat the problem as a grouping task using a hash map. Iterate through each record and compute the session duration duration = out_time - in_time. Use a composite key such as (emp_id, event_day) and accumulate durations in the map. If the key already exists, add the new duration; otherwise initialize it. After processing all rows, convert the map entries into the required result format.

This approach works well in languages like Python or JavaScript where datasets may already be loaded in memory. The hash lookup ensures constant-time updates per record. The overall complexity stays linear because each row is visited once and each map operation is O(1) on average.

Recommended for interviews: Interviewers typically expect the SQL aggregation solution because the problem is tagged as a database question. The key observation is recognizing that the result is a grouped sum across two columns. Showing the hash map implementation demonstrates the same reasoning in a general algorithmic setting. Both approaches rely on the same idea: compute session duration, then aggregate by employee and day.

Approach 1: Approach 1: SQL Aggregation

This approach involves using SQL capabilities to group and aggregate data based on unique 'day' and 'emp_id'. The total time spent by each employee on each day is obtained by summing the difference between 'out_time' and 'in_time' for each of their records. The SQL GROUP BY clause is utilized to group the data.

In this SQL query:

  • The event_day is aliased as day for the result set.
  • The SUM() function calculates the total minutes spent by each employee by summing the differences of out_time and in_time for each record, grouped by event_day and emp_id.

Code

SQL

SQL

Complexity

The time complexity depends on the SQL database engine but is generally O(n log n) due to grouping. There is no extra space complexity aside from the result set.

Try this approach in the editor →

Approach 2: Approach 2: Programmatic Aggregation

This approach uses standard programming techniques to aggregate data. The process involves:

  • Reading the input data into a list of objects or dictionaries.
  • Iterating through the list to calculate the time spent by each employee each day.
  • Storing the results in an appropriate data structure like a dictionary, using a tuple of (day, emp_id) as the key.

This Python function takes a list of employee records and calculates total time per employee per day:

  • A dictionary result tracks the cumulative times, keyed by a tuple of (day, emp_id).
  • For each entry, the difference between out_time and in_time is added to the existing value or initialized as the starting value if the key is absent.
  • The function returns a list of dictionaries with day, emp_id, and total_time.

Code

PythonJavaScript

Python

JavaScript

Complexity

The time complexity is O(n) where n is the number of records, as it involves iterating through each record once. The space complexity is also O(n) due to the storage in the result dictionary.

Try this approach in the editor →

Approach 3: Group By + Sum Function

We can first group by emp_id and event_day, and then calculate the total time for each group. The total time is equal to the sum of the differences between out_time and in_time for each record in the group.

Code

MySQL

MySQL

Try this approach in the editor →

Complexity Comparison

ApproachComplexity
Approach 1: SQL Aggregation

The time complexity depends on the SQL database engine but is generally O(n log n) due to grouping. There is no extra space complexity aside from the result set.

Approach 2: Programmatic Aggregation

The time complexity is O(n) where n is the number of records, as it involves iterating through each record once. The space complexity is also O(n) due to the storage in the result dictionary.

Group By + Sum Function—

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
SQL AggregationO(n)O(1)Best for database queries where grouping and summation can be done directly in SQL.
Programmatic Aggregation (Hash Map)O(n)O(n)Useful when processing records in application code using Python or JavaScript.

Video Solution

LeetCode 1741: Find Total Time Spent by Each Employee [SQL] • Frederik Müller • 5,074 views views

Watch 9 more video solutions →

Frequently Asked Questions

Is Find Total Time Spent by Each Employee easy or hard?
LeetCode classifies this problem as Easy with a high acceptance rate. The challenge mainly checks familiarity with SQL aggregation and grouping rather than complex algorithms.
Find Total Time Spent by Each Employee Python/Java solution
In Python or JavaScript, iterate through the records, compute duration = out_time - in_time, and store totals in a dictionary keyed by employee id and day. Update the running sum for each key and output the aggregated results after processing all rows.
How to solve Find Total Time Spent by Each Employee in O(n)?
Process each session once and aggregate durations by employee and day. In SQL, use SUM(out_time - in_time) with GROUP BY emp_id, event_day. In code, iterate through records and accumulate durations in a hash map keyed by (emp_id, event_day).
What is the best approach for Find Total Time Spent by Each Employee?
SQL aggregation is the most direct solution. Compute each session duration using (out_time - in_time) and use GROUP BY emp_id and event_day with SUM to accumulate total time. The database handles grouping efficiently in O(n) time.
Is Find Total Time Spent by Each Employee asked at Google/Amazon/Meta?
This problem represents a common SQL interview pattern used by companies such as Amazon, Google, and Meta: aggregating event logs to compute metrics per user and per day. Variants often appear in data engineering and analytics interviews.
What data structure is used in Find Total Time Spent by Each Employee?
The SQL solution relies on database aggregation using GROUP BY. In programmatic implementations, a hash map (dictionary) stores accumulated durations for each (emp_id, event_day) pair, enabling constant‑time updates.
What is the time complexity of Find Total Time Spent by Each Employee?
The optimal solution runs in O(n) time where n is the number of rows in the Employees table. Each row is scanned once and aggregated into a grouped result. SQL engines or hash map implementations both maintain linear complexity.

Ready to solve this problem?

Practice Find Total Time Spent by Each Employee with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary