Domain 1EasyCORE

Find Total Time Spent by Each Employee

**Restated Problem:** Given two tables, `Employees` and `TimeSpent`, we need to calculate the total time spent by each employee on various tasks. The `Employees` table contains employee details, and the `TimeSpent` table records the time spent by each employee on different tasks.

Domain: Easy•Updated: 2026-08-31
#Algorithms#LeetCode#qwen2.5-coder:14b#**FAANG asked:** 1

1741. Find Total Time Spent by Each Employee

Model: qwen2.5-coder:14b
Topics:
FAANG asked: 1
Acceptance rate: 91.2%


Problem Breakdown

Restated Problem: Given two tables, Employees and TimeSpent, we need to calculate the total time spent by each employee on various tasks. The Employees table contains employee details, and the TimeSpent table records the time spent by each employee on different tasks.

Inputs:

  • Employees table with columns: employee_id, name.
  • TimeSpent table with columns: employee_id, task_id, time_spent.

Outputs:

  • A result set with columns: employee_id, total_time_spent.

Implicit Constraints/Edge Cases:

  • The Employees table might be empty.
  • The TimeSpent table might be empty or contain entries for employees not present in the Employees table.
  • There could be multiple entries for the same employee and task, which should be summed up.

Intuition

Brute Force Approach:

  1. Iterate over each employee in the Employees table.
  2. For each employee, iterate over all entries in the TimeSpent table to sum up the time spent.
  3. Store the total time for each employee.

This approach is inefficient with a time complexity of O(n * m), where n is the number of employees and m is the number of time entries. It also requires multiple scans through the data, which is not optimal.

Key Insight: We can leverage SQL’s aggregation capabilities to efficiently sum up the time spent by each employee using a single query. This approach will have a better time complexity and utilize the database’s optimized execution plans.

Approach

  1. Join Tables: Perform an inner join between the Employees and TimeSpent tables on the employee_id column to combine relevant data.
  2. Aggregate Data: Use the GROUP BY clause to group the results by employee_id.
  3. Sum Time Spent: Use the SUM() function to calculate the total time spent for each employee.
  4. Select Required Columns: Select the employee_id and the calculated total time spent.

Complexity Analysis

  • Time Complexity: O(n + m), where n is the number of employees and m is the number of time entries. The join operation and aggregation are efficient operations in SQL.
  • Space Complexity: O(1) for the query execution, as we are only storing the result set.

Solution

# SQL Query to find total time spent by each employee
SELECT e.employee_id, SUM(t.time_spent) AS total_time_spent
FROM Employees e
JOIN TimeSpent t ON e.employee_id = t.employee_id
GROUP BY e.employee_id;

Edge Cases Handled

  • Empty Employees Table: The query will return an empty result set as there are no employees to process.
  • Empty TimeSpent Table: The query will return an empty result set as there is no time data to aggregate.
  • Missing Employees in TimeSpent: An inner join ensures that only employees with recorded time entries are included in the results.
  • Multiple Entries for Same Employee and Task: The SUM() function correctly aggregates all time entries for each employee.