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
This approach involves joining the Project and Employee tables based on employee_id and then grouping by project_id to calculate the average experience years for employees in each project.
In this solution, we use an INNER JOIN to combine the Project table with the Employee table on employee_id. This joins each employee to their respective projects. Next, we group the resulting records by project_id and calculate the average of experience_years for each group. The ROUND function is used to round the result to two decimal places.
Time Complexity: O(n), where n is the number of rows in the joined table. Space Complexity: O(1), assuming that the storage needed for the result is not considered as extra space.
In this approach, we find the average experience years using a subquery to aggregate data from the Employee table after filtering it by matching project_id from the Project table.
This solution first selects the distinct project_id from the Project table. For each project, a subquery is run which selects experience_years from the Employee table but filtered by employees that are part of the current project. The average experience of these employees is calculated by the subquery and rounded to two decimal places.
Time Complexity: O(n * m), where n is the number of projects and m is the number of employees per project on average. Space Complexity: O(1), assuming that the storage needed for the result is not considered as extra space.
This approach leverages SQL JOIN to combine the Project and Employee tables, followed by the use of AVG function to calculate the average experience years for each project, and finally rounding the result to 2 decimal places using the ROUND function.
In this solution, we use a JOIN operation to link the Project and Employee tables through employee_id. We then utilize the AVG function on the experience_years column to compute the average for each project_id. The GROUP BY clause ensures aggregation by project, and the ROUND function is used to format the average to two decimal places.
Time Complexity: O(N), where N is the total number of rows in the joined table.
Space Complexity: O(M), where M is the number of unique projects.
This approach utilizes a subquery to first collect the employee experience for each project and then calculates the average in the outer query.
The inner subquery first performs a JOIN to collect employee experience associated with each project. The outer query then aggregates this data using the GROUP BY clause on project_id and computes the average experience using AVG, rounded to two decimal places with ROUND.
Time Complexity: O(N), as we traverse through the joined data once.
Space Complexity: O(M), where M is the number of unique projects.
| Approach | Complexity |
|---|---|
| Using SQL JOIN and Aggregation | Time Complexity: O(n), where n is the number of rows in the joined table. Space Complexity: O(1), assuming that the storage needed for the result is not considered as extra space. |
| Subquery with Aggregation | Time Complexity: O(n * m), where n is the number of projects and m is the number of employees per project on average. Space Complexity: O(1), assuming that the storage needed for the result is not considered as extra space. |
| SQL JOIN with Average Function | Time Complexity: O(N), where N is the total number of rows in the joined table. |
| Subquery for Aggregation | Time Complexity: O(N), as we traverse through the joined data once. |
Project Employees I | Leetcode 1075 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 5,454 views views
Watch 9 more video solutions →Practice Project Employees I with our built-in code editor and test cases.
Practice on FleetCode