Project Employees I - Problem

You are given two tables: Project and Employee.

The Project table contains information about which employees are working on each project, where (project_id, employee_id) is the primary key.

The Employee table contains information about each employee, including their experience_years.

Write an SQL query that reports the average experience years of all employees for each project, rounded to 2 decimal places.

Return the result table in any order.

Table Schema

Project
Column Name Type Description
project_id PK int Project identifier
employee_id PK int Employee identifier, foreign key to Employee table
Primary Key: (project_id, employee_id)
Note: Each row indicates that an employee is working on a project
Employee
Column Name Type Description
employee_id PK int Employee identifier, primary key
name varchar Employee name
experience_years int Years of experience (guaranteed not NULL)
Primary Key: employee_id
Note: Contains information about each employee including their experience

Input & Output

Example 1 — Basic Project Experience Calculation
Input Tables:
Project
project_id employee_id
1 1
1 2
1 3
2 1
2 4
Employee
employee_id name experience_years
1 Khaled 3
2 Ali 2
3 John 1
4 Doe 2
Output:
project_id average_years
1 2
2 2.5
💡 Note:

For project 1: employees 1, 2, 3 have experience years (3, 2, 1), so average = (3+2+1)/3 = 2.00. For project 2: employees 1, 4 have experience years (3, 2), so average = (3+2)/2 = 2.50.
Example 2 — Single Employee Project
Input Tables:
Project
project_id employee_id
1 1
2 2
Employee
employee_id name experience_years
1 Alice 5
2 Bob 8
Output:
project_id average_years
1 5
2 8
💡 Note:

Each project has only one employee, so the average equals the individual employee's experience years.

Constraints

  • 1 ≤ project_id ≤ 100
  • 1 ≤ employee_id ≤ 1000
  • 0 ≤ experience_years ≤ 50
  • experience_years is guaranteed to be not NULL

Visualization

Tap to expand
Project Employees Average Experience CalculatorInput TablesProjectproj_idemp_id111223Employeeemp_idexp_yrs132231JOIN ONemployee_idGROUP BYproject_idOutputproject_idaverage_years12.0021.00Project 1: AVG(3,2,1) = 2.00 | Project 2: AVG(1) = 1.00
Understanding the Visualization
1
Join Tables
Connect Project and Employee data
2
Group by Project
Aggregate employees by project_id
3
Calculate Average
Apply AVG() and ROUND() functions
Key Takeaway
🎯 Key Insight: Use JOIN to combine related data, then GROUP BY with aggregate functions for calculations

Related Problems

Asked in
Amazon 12 Facebook 8 Microsoft 6
25.6K Views
Medium Frequency
~8 min Avg. Time
895 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen