Find Total Time Spent by Each Employee

Find Total Time Spent by Each Employee

1. What is this problem about?

The Find Total Time Spent by Each Employee interview question is a SQL aggregation task. You are given a Employees table that logs every entry and exit of an employee, including the day, their ID, and the time they checked in and out. Your task is to calculate the total time each employee spent in the office on each unique day.

2. Why is this asked in interviews?

Companies like Amazon and Google use the Find Total Time Spent coding problem to assess basic knowledge of Grouping and Multi-column Aggregation. It tests whether you can group data by two distinct keys (day and employee) and perform arithmetic on columns within an aggregate function (SUM(out - in)).

3. Algorithmic pattern used

This problem follows the Multi-group Aggregation pattern.

1. Group By: Group the rows by both event_day and emp_id.
2. Calculate Duration: For each row, the time spent is out_time - in_time.
3. Sum: Sum these durations within each group.
4. Alias: Return the day, the employee ID, and the total time (usually aliased as total_time).

4. Example explanation

Log:

• Day 1, Emp 1: 10 to 20.
• Day 1, Emp 1: 30 to 45.
• Day 1, Emp 2: 0 to 50.

1. Group (Day 1, Emp 1): $(20 - 10) + (45 - 30) = 10 + 15 = 25$.
2. Group (Day 1, Emp 2): $(50 - 0) = 50$. Result: (Day 1, 1, 25), (Day 1, 2, 50).

5. Common mistakes candidates make

• Grouping only by employee: Forgetting that an employee might work on different days, so the day must be part of the GROUP BY clause.
• Incorrect Math: Adding the check-in times and subtracting the check-out times (instead of vice versa).
• Distinct Day handling: Not correctly handling the date format if it's a full timestamp instead of just a day.

6. Interview preparation tip

Always look at the "unique keys" in the requested output. If the output asks for "each employee on each day," that tells you exactly what columns need to be in your GROUP BY statement. This is a fundamental Database interview pattern.