Description

Table: Project

Column NameType
project_idint
employee_idint
  • (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 NameType
employee_idint
namevarchar
experience_yearsint
  • 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.

Problem Statement

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_idemployee_id
11
12
13
21
24
  • Employee table:
employee_idnameexperience_years
1Khaled3
2Ali2
3John1
4Doe2

Output:

project_idaverage_years
12.00
22.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

Solution

In order to find average_years, you need information of experience_years for each employee_id. You have to join Project table with Employee table. Then, you need to group the data by project_id and calculate the average of experience_years for each group. Finally, you need to round the average_years to two decimal places.

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