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.
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:
event_day is aliased as day for the result set.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.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.
This approach uses standard programming techniques to aggregate data. The process involves:
This Python function takes a list of employee records and calculates total time per employee per day:
result tracks the cumulative times, keyed by a tuple of (day, emp_id).out_time and in_time is added to the existing value or initialized as the starting value if the key is absent.day, emp_id, and total_time.JavaScript
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.
| Approach | Complexity |
|---|---|
| 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. |
1 secret trick to get better at Leetcode! • Top SWE • 21,586 views views
Watch 9 more video solutions →Practice Find Total Time Spent by Each Employee with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor