Project Employees I | LeetCode | MSSQL

The Problem

Consider the following tables in our 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. 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, and experience_years is not NULL. Each row of this table contains information about one employee.

The challenge is to write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits. The result should be returned in any order.

Explanation

For example, consider the following data:

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

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.

The Solution

There are a few ways to tackle this problem, and we will discuss two major ones in this post.

Source Code 1: Using Window Functions

The first approach leverages SQL's window functions, particularly AVG() with PARTITION BY.

This query uses a JOIN operation to combine the Project and Employee tables, then computes the average of experience_years for each project_id using the AVG() function in combination with the PARTITION BY clause.

SELECT DISTINCT
    p.project_id,
    ROUND(AVG(e.experience_years * 1.0) OVER (PARTITION BY p.project_id), 2) [average_years]
FROM
    Project p JOIN Employee e ON p.employee_id = e.employee_id

This code runs in 4258ms and beats 26.23% of other submissions.

Source Code 2: Using GROUP BY

The second approach uses a GROUP BY clause to achieve similar results. After joining the Project and Employee tables, it groups the result by project_id and then computes the average of experience_years for each group.

SELECT
    p.project_id,
    ROUND(AVG(e.experience_years * 1.0), 2) [average_years]
FROM
    Project p JOIN Employee e ON p.employee_id = e.employee_id
GROUP BY p.project_id

This code runs in 4552ms and beats 15.44% of other submissions.

Conclusion

Both of these methods achieve the desired result, but with different performance results. While the window function solution tends to be slower due to the overhead of partitioning, the GROUP BY solution can be faster but may also consume more memory due to grouping.

In this case, Source Code 1, which uses window functions, performs better than Source Code 2, which uses a GROUP BY clause. However, performance can vary depending on the specific RDBMS and data distribution. It's crucial to understand your data and SQL engine to select the best approach for your needs.

The original problem can be found at LeetCode.

For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.

ranggakd - Link in Bio & Creator Tools | Beacons

@ranggakd | center details summary summary Oh hello there I m a an Programmer AI Tech Writer Data Practitioner Statistics Math Addict Open Source Contributor Quantum Computing Enthusiast details center.

beacons.ai