Find Total Time Spent by Each Employee - Solution & Explanation

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.