Skip to content

LeetCode 1075: Project Employees I

A clear explanation of computing the average years of experience per project using JOIN and AVG aggregation.

leetcode database

Problem Restatement

We have two tables:

Project(project_id, employee_id) — maps employees to projects.

Employee(employee_id, name, experience_years) — employee details.

We need to report the average experience years for each project, rounded to 2 decimal places.

Edge Cases

  • Match the aggregate to the question: use raw COUNT or SUM for rows/amounts, and COUNT(DISTINCT ...) only for unique entities.
  • Tie cases should return every qualifying row unless the statement asks for a single row.
  • For outer joins, keep filters on the joined table in the ON clause when unmatched rows must remain visible.

Common Pitfalls

  • Do not put aggregate filters in WHERE; use HAVING after GROUP BY.
  • When the answer needs all tied winners, avoid LIMIT 1 unless the statement explicitly asks for one row.

Implementation 

SELECT p.project_id,
       ROUND(AVG(e.experience_years), 2) AS average_years
FROM Project p
JOIN Employee e ON p.employee_id = e.employee_id
GROUP BY p.project_id;

Code Explanation

Join Project with Employee on employee_id to get experience years for each project member.

GROUP BY project_id groups each project’s employees.

AVG(experience_years) computes the average, and ROUND(..., 2) formats to 2 decimal places.