Problem

Table: Project

+————-+———+ | Column Name | Type | +————-+———+ | project_id | int | | employee_id | int | +————-+———+ (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 Name | Type | +——————+———+ | employee_id | int | | name | varchar | | experience_years | int | +——————+———+ 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.

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.

Examples

Example 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

Input: 
Project table:
+-------------+-------------+
| project_id  | employee_id |
+-------------+-------------+
| 1           | 1           |
| 1           | 2           |
| 1           | 3           |
| 2           | 1           |
| 2           | 4           |
+-------------+-------------+
Employee table:
+-------------+--------+------------------+
| employee_id | name   | experience_years |
+-------------+--------+------------------+
| 1           | Khaled | 3                |
| 2           | Ali    | 2                |
| 3           | John   | 1                |
| 4           | Doe    | 2                |
+-------------+--------+------------------+
Output: 
+-------------+---------------+
| project_id  | average_years |
+-------------+---------------+
| 1           | 2.00          |
| 2           | 2.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

### Method 1 – Join and Group By with AVG

#### Intuition

We need to compute the average experience years for each project. This is a join and group-by-aggregate problem, solved by joining the tables and grouping by project.

#### Approach

1. Join `Project` and `Employee` on `employee_id`.
2. Group by `project_id` and compute the average of `experience_years`, rounding to 2 decimal places.

#### Code



    

         
         
         
        
            
            
        
            
            
                 
                 
                
            
        
            
            
        
            
            
                 
                 
                
            
        
            
            
        
            
            
                 
                 
                
            
        
            
            
        
    


    

        
         
        
        
         
         
        

             
            
             
            
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
            
            
            




1
2
3
4




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;





        

        
        
         
        
        
         
         
        

             
            
             
            
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
            
            
            




1
2
3
4




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;





        

        
        
         
        
        
         
         
        

             
            
             
            
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
            
            
            




1
2
3
4
5
6




# Assume Project and Employee are pandas DataFrames
import pandas as pd
def average_experience_by_project(Project: pd.DataFrame, Employee: pd.DataFrame) -> pd.DataFrame:
    merged = Project.merge(Employee, on='employee_id')
    result = merged.groupby('project_id')['experience_years'].mean().round(2).reset_index(name='average_years')
    return result





        

        
        
         
        
        
    





#### Complexity

* ⏰ Time complexity: `O(N)` where N is the number of rows in Project.
* 🧺 Space complexity: `O(P)` where P is the number of projects.