Retiago Drago

Posted on

# 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.