Find the average experience of employees working on each project, rounded to two decimals.
You are given two tables: one lists projects and the employees assigned to them, and the other stores employee details including their years of experience.
For every project, compute the average years of experience of all employees assigned to that project. Return the project identifier and the average experience rounded to 2 decimal places.
The result should include one row per project that appears in the assignment table.
Input Format
Tables
Project(project_id, employee_id)
project_id: identifier of a projectemployee_id: identifier of an employee assigned to the project
Employee(employee_id, name, experience_years)
employee_id: unique employee identifierexperience_years: years of experience for that employee
Output Format
Return a table with:
project_idaverage_years(rounded to 2 decimal places)
Rows may be returned in any order unless otherwise specified.
Constraints
- Each employee referenced in
Projecthas a matching row inEmployee. - A project may have one or more assigned employees.
- Round the average to 2 decimal places.
Example 1
Input
Project +------------+-------------+ | project_id | employee_id | +------------+-------------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 4 | +------------+-------------+ Employee +-------------+-------+------------------+ | employee_id | name | experience_years | +-------------+-------+------------------+ | 1 | Alice | 3 | | 2 | Bob | 2 | | 3 | Carol | 5 | | 4 | David | 6 | +-------------+-------+------------------+
Output
+------------+---------------+ | project_id | average_years | +------------+---------------+ | 1 | 3.33 | | 2 | 4.50 | +------------+---------------+
Explanation
Project 1 has employees with experience 3, 2, and 5, so the average is 10 / 3 = 3.33. Project 2 has employees with experience 3 and 6, so the average is 4.50.
Premium problem context
Unlock deeper context for this problem
Premium adds guided hints, editorial links, similar variants, discussion resources, and concept maps so you can understand why a problem matters, not just solve it once.