Project Team Experience Averages

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

project =

[{"project_id":1,"employee_id":1},{"project_id":1,"employee_id":2},{"project_id":1,"employee_id":3},{"project_id":2,"employee_id":1},{"project_id":2,"employee_id":4}]

employee =

[{"employee_id":1,"name":"Nia","experience_years":3},{"employee_id":2,"name":"Omar","experience_years":2},{"employee_id":3,"name":"Luca","experience_years":1},{"employee_id":4,"name":"Mei","experience_years":2}]

Project Team Experience Averages

Hints

Project Team Experience Averages

Hints