Skip to main content
Talentd

Project Employees I - Solution & Explanation

EasyDatabase6 min readAsked at: Amazon, Microsoft, Meta +3
Practice this problem

Problem Statement

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

Approach Overview

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 1: Using SQL JOIN and Aggregation

This approach involves joining the Project and Employee tables based on employee_id and then grouping by project_id to calculate the average experience years for employees in each project.

In this solution, we use an INNER JOIN to combine the Project table with the Employee table on employee_id. This joins each employee to their respective projects. Next, we group the resulting records by project_id and calculate the average of experience_years for each group. The ROUND function is used to round the result to two decimal places.

Code

SQL

SQL

Complexity

Time Complexity: O(n), where n is the number of rows in the joined table. Space Complexity: O(1), assuming that the storage needed for the result is not considered as extra space.

Try this approach in the editor →

Approach 2: Subquery with Aggregation

In this approach, we find the average experience years using a subquery to aggregate data from the Employee table after filtering it by matching project_id from the Project table.

This solution first selects the distinct project_id from the Project table. For each project, a subquery is run which selects experience_years from the Employee table but filtered by employees that are part of the current project. The average experience of these employees is calculated by the subquery and rounded to two decimal places.

Code

SQL

SQL

Complexity

Time Complexity: O(n * m), where n is the number of projects and m is the number of employees per project on average. Space Complexity: O(1), assuming that the storage needed for the result is not considered as extra space.

Try this approach in the editor →

Approach 3: SQL JOIN with Average Function

This approach leverages SQL JOIN to combine the Project and Employee tables, followed by the use of AVG function to calculate the average experience years for each project, and finally rounding the result to 2 decimal places using the ROUND function.

In this solution, we use a JOIN operation to link the Project and Employee tables through employee_id. We then utilize the AVG function on the experience_years column to compute the average for each project_id. The GROUP BY clause ensures aggregation by project, and the ROUND function is used to format the average to two decimal places.

Code

SQL

SQL

Complexity

Time Complexity: O(N), where N is the total number of rows in the joined table.
Space Complexity: O(M), where M is the number of unique projects.

Try this approach in the editor →

Approach 4: Subquery for Aggregation

This approach utilizes a subquery to first collect the employee experience for each project and then calculates the average in the outer query.

The inner subquery first performs a JOIN to collect employee experience associated with each project. The outer query then aggregates this data using the GROUP BY clause on project_id and computes the average experience using AVG, rounded to two decimal places with ROUND.

Code

SQL

SQL

Complexity

Time Complexity: O(N), as we traverse through the joined data once.
Space Complexity: O(M), where M is the number of unique projects.

Try this approach in the editor →

Approach 5: Default Approach

Code

MySQL

MySQL

Try this approach in the editor →

Complexity Comparison

ApproachComplexity
Using SQL JOIN and Aggregation

Time Complexity: O(n), where n is the number of rows in the joined table. Space Complexity: O(1), assuming that the storage needed for the result is not considered as extra space.

Subquery with Aggregation

Time Complexity: O(n * m), where n is the number of projects and m is the number of employees per project on average. Space Complexity: O(1), assuming that the storage needed for the result is not considered as extra space.

SQL JOIN with Average Function

Time Complexity: O(N), where N is the total number of rows in the joined table.
Space Complexity: O(M), where M is the number of unique projects.

Subquery for Aggregation

Time Complexity: O(N), as we traverse through the joined data once.
Space Complexity: O(M), where M is the number of unique projects.

Default Approach—

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
SQL JOIN with GROUP BYO(n)O(1)Best general solution; clear and efficient aggregation across joined tables
Subquery with AggregationO(n)O(1)When separating project selection from aggregation logic
JOIN with AVG FunctionO(n)O(1)Standard relational aggregation pattern used in many SQL interview problems
Derived Table SubqueryO(n)O(1)Useful when aggregated results must be reused or filtered further

Video Solution

Project Employees I | Leetcode 1075 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 11,586 views views

Watch 9 more video solutions →

Frequently Asked Questions

Is Project Employees I easy or hard?
Project Employees I is categorized as an Easy problem with an acceptance rate above 60%. It mainly checks familiarity with SQL JOIN operations and basic aggregation using GROUP BY and AVG().
Project Employees I Python/Java solution
This is primarily a SQL problem on LeetCode. Instead of implementing logic in Python or Java, you write a SQL query that joins the Project and Employee tables and calculates the average experience per project using AVG() and GROUP BY.
How to solve Project Employees I in O(n)?
Join the Project table with the Employee table on employee_id, then group the results by project_id. Use AVG(experience_years) to compute the average experience for each group. Database engines perform the join and aggregation in a single pass over the dataset, resulting in O(n) time complexity.
What is the best approach for Project Employees I?
The best approach uses a SQL JOIN between the Project and Employee tables followed by GROUP BY project_id and the AVG() aggregation function. This method scans the joined rows once and computes the average experience per project efficiently in O(n) time.
Is Project Employees I asked at Google/Amazon/Meta?
SQL aggregation and join problems like Project Employees I appear frequently in database interviews at companies such as Amazon, Google, and Meta. The question tests your ability to combine tables and compute grouped metrics using SQL.
What data structure is used in Project Employees I?
The problem relies on relational database tables and SQL operations rather than traditional data structures. The main concepts are table joins, grouping with GROUP BY, and aggregation using functions like AVG().
What is the time complexity of Project Employees I?
Most SQL solutions run in O(n) time where n is the number of rows processed after the join between Project and Employee tables. Aggregation with GROUP BY operates over the joined dataset and requires constant additional memory.

Ready to solve this problem?

Practice Project Employees I with our built-in code editor and test cases.

Practice on FleetCode
Previous

Project Employees II