{x}
blog image

Project Employees I

Table: Project

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| project_id  | int     |
| employee_id | int     |
+-------------+---------+
(project_id, employee_id) is the primary key of this table.
employee_id is a foreign key to Employee table.
Each row of this table indicates that the employee with employee_id is working on the project with project_id.

 

Table: Employee

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| employee_id      | int     |
| name             | varchar |
| experience_years | int     |
+------------------+---------+
employee_id is the primary key of this table. It's guaranteed that experience_years is not NULL.
Each row of this table contains information about one employee.

 

Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits.

Return the result table in any order.

The query result format is in the following example.

 

Example 1:

Input: 
Project table:
+-------------+-------------+
| project_id  | employee_id |
+-------------+-------------+
| 1           | 1           |
| 1           | 2           |
| 1           | 3           |
| 2           | 1           |
| 2           | 4           |
+-------------+-------------+
Employee table:
+-------------+--------+------------------+
| employee_id | name   | experience_years |
+-------------+--------+------------------+
| 1           | Khaled | 3                |
| 2           | Ali    | 2                |
| 3           | John   | 1                |
| 4           | Doe    | 2                |
+-------------+--------+------------------+
Output: 
+-------------+---------------+
| project_id  | average_years |
+-------------+---------------+
| 1           | 2.00          |
| 2           | 2.50          |
+-------------+---------------+
Explanation: The average experience years for the first project is (3 + 2 + 1) / 3 = 2.00 and for the second project is (3 + 2) / 2 = 2.50

Solution Explanation for LeetCode 1075: Project Employees I

This problem requires us to calculate the average experience years of employees for each project, rounded to two decimal places. We are given two tables: Project and Employee.

Tables:

  • Project: project_id, employee_id (primary key: (project_id, employee_id))
  • Employee: employee_id (primary key), name, experience_years

Approach:

The solution involves joining the Project and Employee tables based on employee_id, then grouping the results by project_id to calculate the average experience_years for each project. Finally, we round the average to two decimal places using the ROUND() function.

SQL Query (MySQL):

SELECT project_id, ROUND(AVG(experience_years), 2) AS average_years
FROM
    Project
    JOIN Employee USING (employee_id)
GROUP BY project_id;

Explanation:

  1. SELECT project_id, ROUND(AVG(experience_years), 2) AS average_years: This selects the project_id and calculates the average of experience_years, rounding it to 2 decimal places using ROUND(). The result is aliased as average_years.

  2. FROM Project JOIN Employee USING (employee_id): This performs an INNER JOIN between the Project and Employee tables using the employee_id as the common column. The USING clause is a shorthand for specifying the join condition when the column name is the same in both tables. This efficiently combines data from both tables where employees are working on projects.

  3. GROUP BY project_id: This groups the results by project_id, so the AVG() function calculates the average experience years for each project separately.

Time Complexity Analysis:

The time complexity of this SQL query depends on the database system's query optimizer and the size of the tables. However, a reasonable estimate is O(N log N) or O(N), where N is the number of rows in the larger of the two tables (Project or Employee). The join operation typically takes at least O(N log N) using efficient join algorithms (like merge join or hash join). Grouping and aggregation operations are also relatively efficient, often with linear time complexity. The exact complexity might differ slightly based on indexing and database optimization techniques.

Space Complexity Analysis:

The space complexity depends on the size of the intermediate result set after the join and before the grouping. In the worst case, if every employee works on every project (a fully connected relationship), the space complexity would be proportional to the product of the number of projects and employees. However, in practical scenarios, it's usually much smaller than O(M * N), where M is the number of projects and N is the number of employees. The space complexity is dominated by the size of the result set (which is at most the number of projects) and some temporary space used for intermediate calculations during the query execution.