Project Employees I

Difficulty: Easy | Acceptance: 66.70% | Paid: No Topics: Database

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.

Table: Employee

+------------------+---------+ | Column Name | Type | +------------------+---------+ | employee_id | int | | name | varchar | | experience_years | int | +------------------+---------+ employee_id is the primary key of this table.

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

• Examples

Example 1:

Input: Project table: +-------------+-------------+ | project_id | employee_id | +-------------+-------------+ | 1 | 1 | | 1 | 2 | | 2 | 1 | +-------------+-------------+ Employee table: +-------------+--------+------------------+ | employee_id | name | experience_years | +-------------+--------+------------------+ | 1 | Khaled | 3 | | 2 | Ali | 2 | +-------------+--------+------------------+

Output: +-------------+---------------+ | project_id | average_years | +-------------+---------------+ | 1 | 2.50 | | 2 | 3.00 | +-------------+---------------+ Explanation: The average experience years for the first project is (3 + 2) / 2 = 2.50 and for the second project is (3) / 1 = 3.00.

• Constraints

experience_years is an integer.
The result should be rounded to 2 decimal places.

Inner Join and Group By

Intuition We need to combine the Project and Employee tables to access the experience_years for each employee in a specific project. Then, we group the results by project_id and calculate the average of the experience years.

Steps

• Join the Project table with the Employee table on the employee_id column.
• Group the resulting records by project_id.
• Use the AVG() aggregate function to calculate the average experience years for each group.
• Round the result to 2 decimal places using ROUND().

def solution():
    # LeetCode 1075: Project Employees I
    # This problem requires a SQL query.
    # Since the environment requires Python, we return the query string.
    return "SELECT project_id, ROUND(AVG(experience_years), 2) AS average_years FROM Project JOIN Employee ON Project.employee_id = Employee.employee_id GROUP BY project_id"

Complexity

• Time: O(N + M) where N and M are the number of rows in Project and Employee tables, respectively, assuming efficient indexing on the join keys.
• Space: O(K) where K is the number of unique project IDs for storing the intermediate aggregation results.
• Notes: The INNER JOIN ensures we only consider employees who are assigned to projects and exist in the Employee table.

Implicit Join (WHERE Clause)

Intuition Instead of using the explicit JOIN keyword, we can use a comma-separated list of tables in the FROM clause and specify the join condition in the WHERE clause. This is an older SQL syntax but achieves the same result.

Steps

• Select project_id and the rounded average of experience_years.
• Specify both Project and Employee tables in the FROM clause.
• Filter the records where Project.employee_id matches Employee.employee_id in the WHERE clause.
• Group the results by project_id.

def solution():
    # LeetCode 1075: Project Employees I
    # Using implicit join syntax
    return "SELECT p.project_id, ROUND(AVG(e.experience_years), 2) AS average_years FROM Project p, Employee e WHERE p.employee_id = e.employee_id GROUP BY p.project_id"

Complexity

• Time: O(N + M) similar to the explicit join, as the query optimizer typically treats implicit and explicit joins identically.
• Space: O(K) for the aggregation results.
• Notes: While implicit joins are valid, explicit INNER JOIN syntax is generally preferred for readability and separating join logic from filtering logic.

Common Table Expression (CTE)

Intuition We can use a Common Table Expression (CTE) to first create a temporary result set that joins the two tables. Then, we select from this CTE to perform the aggregation. This improves readability for more complex queries.

Steps

• Define a CTE (e.g., named ProjectExperience) that selects project_id and experience_years by joining Project and Employee.
• Select project_id and the rounded average of experience_years from the ProjectExperience CTE.
• Group the results by project_id.

def solution():
    # LeetCode 1075: Project Employees I
    # Using CTE syntax
    return "WITH ProjectExperience AS (SELECT p.project_id, e.experience_years FROM Project p JOIN Employee e ON p.employee_id = e.employee_id) SELECT project_id, ROUND(AVG(experience_years), 2) AS average_years FROM ProjectExperience GROUP BY project_id"

Complexity

• Time: O(N + M). The CTE is materialized or executed as a stream, and the aggregation follows.
• Space: O(N + M) in the worst case if the CTE is materialized, though modern optimizers often avoid this.
• Notes: CTEs are excellent for code organization and reusing intermediate results, though for this simple query, the performance is identical to the direct join approach.