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
In #1075 Project Employees I, the goal is to compute the average experience years of employees assigned to each project. The information is split across two tables: one mapping employees to projects and another containing employee details. To solve this, the key idea is to combine data from both tables and then calculate an aggregate value.
Start by using a JOIN operation to connect the project assignments with the employee records based on the employee ID. Once the relevant data is merged, group the results by project_id. This allows you to apply the AVG() aggregate function on the employees' experience years for each project. Many solutions also round the result to two decimal places for consistent formatting.
This approach leverages relational database operations efficiently, using grouping and aggregation to summarize employee experience across projects. The database engine handles the heavy lifting during the grouping stage.
| Approach | Time Complexity | Space Complexity |
|---|---|---|
| JOIN with GROUP BY and AVG aggregation | O(n log n) | O(n) |
Learn With Chirag
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.
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.
1SELECT Project.project_id, ROUND(AVG(Employee.experience_years), 2) AS average_years FROM Project INNER JOIN Employee ON Project.employee_id = Employee.employee_id GROUP BY Project.project_id;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.
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.
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.
1SELECT P.project_id, (SELECT ROUND(AVG(E.experience_years), 2) 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.
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.
1SELECT p.project_id, ROUND(AVG(e.experience_years), 2) AS average_years FROM Project p This approach utilizes a subquery to first collect the employee experience for each project and then calculates the average in the outer query.
Time Complexity: O(N), as we traverse through the joined data once.
Space Complexity: O(M), where M is the number of unique projects.
1SELECT project_id, ROUND(AVG(experience_years), 2) AS average_years FROM (SELECT p.project_id,Watch expert explanations and walkthroughs
Practice problems asked by these companies to ace your technical interviews.
Explore More ProblemsJot down your thoughts, approach, and key learnings
This problem mainly uses SQL JOIN operations and aggregate functions like AVG(). It also requires GROUP BY to calculate the average experience for each project separately.
The optimal approach is to join the Project and Employee tables using employee_id, then group the results by project_id. After grouping, apply the AVG() aggregate function to compute the average experience years for employees in each project.
Yes, similar SQL aggregation and join problems frequently appear in technical interviews, especially for data-related roles. They test understanding of relational databases, grouping, and aggregate queries.
The key operation is a relational JOIN between two tables combined with GROUP BY aggregation. These operations allow the database to combine related rows and compute summary statistics efficiently.
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.
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.
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.