Project Team Experience Averages

Easy

A delivery analytics platform stores project staffing in two relational tables: Project(project_id, employee_id): each row means the employee is assigned to that project Employee(employee_id, name, experience_years): each row stores one employee profile The pair (project_id, employee_id) is unique. Your task is to compute project-level average experience: For every project_id, average the experience_years of all employees assigned to that project. Round the computed average to 2 decimal places. Return exactly these columns: project_id average_years Sort the result by project_id in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

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           | Nia    | 3                |
| 2           | Omar   | 2                |
| 3           | Luca   | 1                |
| 4           | Mei    | 2                |

Output

[
  {"project_id":1,"average_years":2.0},
  {"project_id":2,"average_years":2.5}
]

Explanation

Project 1 averages (3+2+1)/3 = 2.00. Project 2 averages (3+2)/2 = 2.50.

Example 2

Input

Project:
| project_id | employee_id |
|------------|-------------|
| 10         | 7           |
| 10         | 9           |
| 11         | 8           |
| 11         | 10          |
| 11         | 11          |

Employee:
| employee_id | name   | experience_years |
|-------------|--------|------------------|
| 7           | Aria   | 0                |
| 8           | Ravi   | 8                |
| 9           | Noor   | 5                |
| 10          | Hana   | 4                |
| 11          | Ivan   | 9                |

Output

[
  {"project_id":10,"average_years":2.5},
  {"project_id":11,"average_years":7.0}
]

Explanation

Only employees listed in each project contribute to that project's average.

Example 3

Input

Project:
| project_id | employee_id |
|------------|-------------|
| 50         | 101         |
| 51         | 101         |
| 51         | 102         |

Employee:
| employee_id | name   | experience_years |
|-------------|--------|------------------|
| 101         | Zoe    | 12               |
| 102         | Theo   | 0                |
| 999         | Unused | 25               |

Output

[
  {"project_id":50,"average_years":12.0},
  {"project_id":51,"average_years":6.0}
]

Explanation

Unassigned employees do not affect any project average.

Constraints

1 <= number of rows in Project <= 10,000
1 <= number of rows in Employee <= 10,000
(project_id, employee_id) is unique in Project
employee_id is unique in Employee
0 <= experience_years <= 1,000,000,000
experience_years is never NULL
Each Project.employee_id references an employee in Employee
Round average_years to 2 decimal places
Return columns exactly as: project_id, average_years
Sort output by project_id ASC
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns project_id and average_years

Project: | project_id | employee_id | |------------|-------------| | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 4 | Employee: | employee_id | name | experience_years | |-------------|--------|------------------| | 1 | Nia | 3 | | 2 | Omar | 2 | | 3 | Luca | 1 | | 4 | Mei | 2 |

Project: | project_id | employee_id | |------------|-------------| | 10 | 7 | | 10 | 9 | | 11 | 8 | | 11 | 10 | | 11 | 11 | Employee: | employee_id | name | experience_years | |-------------|--------|------------------| | 7 | Aria | 0 | | 8 | Ravi | 8 | | 9 | Noor | 5 | | 10 | Hana | 4 | | 11 | Ivan | 9 |

Project: | project_id | employee_id | |------------|-------------| | 50 | 101 | | 51 | 101 | | 51 | 102 | Employee: | employee_id | name | experience_years | |-------------|--------|------------------| | 101 | Zoe | 12 | | 102 | Theo | 0 | | 999 | Unused | 25 |