Calculate Average Experience Per Project in SQL

Question

Given `project` (project_id, employee_id) and `employee` (employee_id, experience_years), write a query to report the average experience years of all employees for each project, rounded to 2 decimal places.

Accepted Answer

Optimal Solution:
SELECT 
  p.project_id,
  ROUND(AVG(e.experience_years)::numeric, 2) AS average_years
FROM project p
JOIN employee e ON p.employee_id = e.employee_id
GROUP BY p.project_id
ORDER BY p.project_id ASC;

Technical Explanation:
Joins project with employee on employee_id, groups by project_id, and computes the rounded average experience years.

project_id	employee_id	experience_years
1	1	3
1	2	2
1	3	1
2	1	3
2	4	6

project_id	average_years
1	2
2	4.5