Watch 10 video solutions for Project Employees I, a easy level problem involving Database. This walkthrough by Learn With Chirag has 10,468 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Project
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| project_id | int |
| employee_id | int |
+-------------+---------+
(project_id, employee_id) is the primary key of this table.
employee_id is a foreign key to Employee table.
Each row of this table indicates that the employee with employee_id is working on the project with project_id.
Table: Employee
+------------------+---------+ | Column Name | Type | +------------------+---------+ | employee_id | int | | name | varchar | | experience_years | int | +------------------+---------+ employee_id is the primary key of this table. It's guaranteed that experience_years is not NULL. Each row of this table contains information about one employee.
Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits.
Return the result table in any order.
The query result format is in the following example.
Example 1:
Input: Project table: +-------------+-------------+ | project_id | employee_id | +-------------+-------------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 4 | +-------------+-------------+ Employee table: +-------------+--------+------------------+ | employee_id | name | experience_years | +-------------+--------+------------------+ | 1 | Khaled | 3 | | 2 | Ali | 2 | | 3 | John | 1 | | 4 | Doe | 2 | +-------------+--------+------------------+ Output: +-------------+---------------+ | project_id | average_years | +-------------+---------------+ | 1 | 2.00 | | 2 | 2.50 | +-------------+---------------+ Explanation: The average experience years for the first project is (3 + 2 + 1) / 3 = 2.00 and for the second project is (3 + 2) / 2 = 2.50
Problem Overview: The Project table maps employees to projects, and the Employee table stores each employee's years of experience. Your task is to return the average experience of employees working on each project. The result should include project_id and the calculated average experience using SQL aggregation.
Approach 1: Using SQL JOIN and Aggregation (O(n) time, O(1) extra space)
The most direct solution joins the Project and Employee tables on employee_id. After the join, each row contains the project and the employee's experience. You then group the rows by project_id and compute the average using the AVG() aggregate function. The key insight is that the join exposes employee experience for each project membership, making aggregation straightforward. This approach relies on core SQL operations: JOIN, GROUP BY, and AVG().
Approach 2: Subquery with Aggregation (O(n) time, O(1) extra space)
A subquery can compute the average experience per project by selecting employees linked to that project. The outer query iterates over distinct project IDs, while the inner query calculates AVG(experience_years) for matching employees. This approach separates the project selection and aggregation logic. It is useful when you want clearer logical separation between filtering and aggregation, though it may be slightly less readable than a direct join depending on the SQL dialect.
Approach 3: SQL JOIN with Average Function (O(n) time, O(1) extra space)
This variation also uses a join but focuses on directly applying AVG(Employee.experience_years) during grouping. The database engine scans the joined dataset once, groups rows by project, and computes the aggregate value. In most relational databases, this execution plan is optimized using indexes on employee_id. It is a common pattern when solving aggregation problems in database interviews.
Approach 4: Subquery for Aggregation (O(n) time, O(1) extra space)
Another variation computes aggregated results inside a derived table (subquery in the FROM clause). The subquery joins the tables and groups by project, while the outer query simply selects the computed values. This technique is helpful when the aggregated result needs further filtering or transformation later. The main concept remains SQL aggregation using GROUP BY.
Recommended for interviews: The JOIN + GROUP BY solution is the expected answer. It demonstrates that you understand relational joins and SQL aggregation patterns. Mentioning a subquery-based alternative shows broader SQL familiarity, but the join-based aggregation is usually the cleanest and most efficient approach.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL JOIN with GROUP BY | O(n) | O(1) | Best general solution; clear and efficient aggregation across joined tables |
| Subquery with Aggregation | O(n) | O(1) | When separating project selection from aggregation logic |
| JOIN with AVG Function | O(n) | O(1) | Standard relational aggregation pattern used in many SQL interview problems |
| Derived Table Subquery | O(n) | O(1) | Useful when aggregated results must be reused or filtered further |