Project Employees I

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You have two tables: Project and Employee. Each row in Project maps an employee to a project. Each employee has an experience_years value. Write a SQL query to report the average experience years of all employees on each project, rounded to 2 decimal places. Return the result in any order.

Database Schema

Project

Column Name	Type	Description
project_id	INT	Part of primary key
employee_id	INT	Part of primary key, FK to Employee

Employee

Column Name	Type	Description
employee_id	INT	Primary key
name	VARCHAR	Employee name
experience_years	INT	Years of experience (≥1)

Example

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

Explanation:

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

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.