Find Total Time Spent by Each Employee | LeetCode | MSSQL

The Problem

The given problem involves a table 'Employees' with the following structure:

Column Name	Type
emp_id	int
event_day	date
in_time	int
out_time	int

Where (emp_id, event_day, in_time) constitutes the primary key. The table records the in-time and out-time of employees for various dates, where 'in_time' and 'out_time' are represented as minutes between 1 and 1440. The task is to calculate the total time spent by each employee on each day. If an employee enters and leaves more than once in a day, each instance should be considered separately.

Explanation

Given the '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

The expected output is:

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

Here, for each employee and each day, we calculate the total time spent in the office by subtracting 'in_time' from 'out_time' for each entry and exit, and then summing these times up.

The Solution

We will look at two different SQL queries that solve this problem and analyze their performance.

Source Code 1

The first solution sums the 'in_time' and 'out_time' for each employee on each day, and then subtracts the sum of 'in_time' from the sum of 'out_time':

SELECT
    event_day [day],
    emp_id,
    SUM(out_time) - SUM(in_time) [total_time]
FROM Employees
GROUP BY
    event_day,
    emp_id

This solution has a runtime of 1031ms, placing it ahead of 17.20% of submissions on LeetCode.

Source Code 2

The second solution differs by first subtracting 'in_time' from 'out_time' for each record, and then summing these values:

SELECT
    event_day [day],
    emp_id,
    SUM(out_time - in_time) [total_time]
FROM Employees
GROUP BY
    event_day,
    emp_id

This solution has a runtime of 828ms, placing it ahead of 38.6% of submissions on LeetCode.

Conclusion

From these solutions, we learn that the choice of SQL constructs and the sequence of operations can impact performance significantly. The second solution performs better, as it avoids the need to separately sum 'in_time' and 'out_time' before subtracting, which can be more computationally intensive. However, the difference in performance can vary depending on the specific data distribution, query optimization of the SQL engine, and other factors.

In terms of LeetCode performance, the solutions rank as follows:

1. Source Code 2
2. Source Code 1

However, it's important to note that LeetCode's performance does not perfectly represent real-world database performance, as many other factors come into play, including indexing, database design, and data distribution.

You can find the original problem at LeetCode.

For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.

👉 Check out all the links on my beacons.ai page 👈