Watch 10 video solutions for Find Total Time Spent by Each Employee, a easy level problem involving Database. This walkthrough by Frederik Müller has 5,045 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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.
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 | Time | Space | When to Use |
|---|---|---|---|
| SQL Aggregation | O(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. |
