Sponsored
Sponsored
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.
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.
1SELECT Project.project_id, ROUND(AVG(Employee.experience_years), 2) AS average_years FROM Project INNER JOIN Employee ON Project.employee_id = Employee.employee_id GROUP BY Project.project_id;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.
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.
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.
1SELECT P.project_id, (SELECT ROUND(AVG(E.experience_years), 2) 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.
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.
1SELECT p.project_id, ROUND(AVG(e.experience_years), 2) AS average_years FROM Project p This approach utilizes a subquery to first collect the employee experience for each project and then calculates the average in the outer query.
Time Complexity: O(N), as we traverse through the joined data once.
Space Complexity: O(M), where M is the number of unique projects.
1SELECT project_id, ROUND(AVG(experience_years), 2) AS average_years FROM (SELECT p.project_id,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.
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.
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.