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

Loading problem...

Preparing your editor. You can read the problem while it loads.

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