Project Employees I | LeetCode 1075

1. Problem Overview & Status Quo

In many relational database management systems and data engineering workloads, calculating metrics across related entities requires joining multiple tables and utilizing aggregate functions. This problem—Project Employees I (LeetCode 1075)—falls under the category of basic aggregate functions. The core objective is to report the average experience years of all employees working on each project, rounded to two decimal places.

2. Core Mental Model & Real-Life Analogy

Real-Life Analogy: Company Project Staffing & Payroll Audits

Imagine you are a department manager auditing a consulting firm. You have two massive binders:

• Binder A (Projects): Lists every project and the ID badges of the employees assigned to work on it.
• Binder B (Employees): Lists every employee's ID badge, their full name, and how many years of professional experience they have.

To find out the average experience level of the team working on a specific client project (e.g., Project 1), you cannot just look at Binder A (which only has numbers like Employee IDs 1, 2, and 3). You have to take each Employee ID, flip open Binder B, look up their individual experience years (e.g., 3, 2, and 1), add those numbers together, and divide by the total number of employees assigned to that project (3).

3. Database Schema & Input Data Structures

We are given two relational tables: Project and Employee.

Table 1: Project

• Role: Indicates that the employee with employee_id is working on the project with project_id.

Table 2: Employee

• Role: Contains biographical and professional experience data for each individual employee.

4. Step-by-Step Mathematical & Logic Breakdown

To compute the average experience years per project, we execute the following logical steps:

1. Table Joining: Connect the Project table with the Employee table using employee_id as the common matching key.
2. Grouping: Group the joined records by project_id so that all employees assigned to the same project are bundled together.
3. Aggregation: Apply the average (AVG()) aggregate function to the experience_years column for each group.
4. Rounding: Wrap the resulting average value inside a ROUND(..., 2) function to restrict output to two decimal places.

Example Walkthrough

Consider the following input datasets:

Project Table:

Employee Table:

Calculation for project_id = 1:

• Associated Employee IDs: 1, 2, 3
• Experience years: 3 (for Emp 1), 2 (for Emp 2), 1 (for Emp 3)
• Sum: $3 + 2 + 1 = 6$
• Count: $3$
• Average: $\frac{6}{3} = 2.00$

Calculation for project_id = 2:

• Associated Employee IDs: 1, 4
• Experience years: 3 (for Emp 1), 2 (for Emp 4)
• Sum: $3 + 2 = 5$
• Count: $2$
• Average: $\frac{5}{2} = 2.50$

5. Naive Approach vs. Production-Ready SQL Query

Naive / Flawed Approaches

• Missing GROUP BY: Omitting GROUP BY project_id while attempting to use AVG() will throw a syntax or grouping error in standard SQL engines because non-aggregated columns cannot exist alongside aggregate functions without explicit grouping.
• Inner Join vs. Left Join: While inner joins work if every project row strictly references a valid employee, using a LEFT JOIN ensures robust handling if projects exist with unassigned or loosely referenced staff entries.

Production-Ready SQL Implementation

SELECT 
    p.project_id,
    ROUND(AVG(e.experience_years), 2) AS average_years
FROM 
    Project p
LEFT JOIN 
    Employee e 
    ON p.employee_id = e.employee_id
GROUP BY 
    p.project_id;

6. Detailed Code Explanation & Execution Flow

Let's trace how the production SQL query processes data step by step:

1. FROM Project p LEFT JOIN Employee e ON p.employee_id = e.employee_id:

   • Establishes Project as the left driving table (p) and Employee as the right joined table (e).
   • Matches rows where p.employee_id equals e.employee_id.

2. GROUP BY p.project_id:

   • Collects all matched employee records belonging to the same project_id into logical partitions or buckets.

3. AVG(e.experience_years):

   • Computes the mathematical mean of the experience_years column across all rows inside each individual project partition.

4. ROUND(..., 2):

   • Takes the raw floating-point average and rounds it to exactly 2 decimal places to satisfy formatting requirements.

5. AS average_years:

   • Aliases the resulting calculation column to match expected return schemas.

7. Real-World Practical Pitfalls & Edge Cases

• Division by Zero: If a project had zero employees assigned (NULL employee match), standard SQL aggregate functions (AVG) safely evaluate to NULL rather than crashing with a division-by-zero error.
• Null Experience Years: Although the problem schema guarantees experience_years is never NULL, in production environments, missing data can skew averages. Always verify whether NULL values are filtered out or treated as zero depending on business rules.
• Data Types: Ensure integer division issues are avoided. MySQL and PostgreSQL handle AVG() by automatically promoting integer sums to floating-point numbers or decimals before division.

8. Summary and Key Takeaways

• Relational Joins: Use LEFT JOIN or INNER JOIN to bridge mapping tables (Project) with entity tables (Employee).
• Aggregation Power: Combine AVG() with GROUP BY to collapse multi-row entity mappings into single-row summary metrics per group.
• Precision Formatting: Use ROUND(expression, decimals) to format floating-point outputs cleanly for reporting layers.
• Primary & Foreign Keys: Understanding composite primary keys and foreign key constraints helps determine correct join conditions without redundant duplicates.
• Clean Aliasing: Use table aliases (p for Project, e for Employee) to keep complex queries readable and maintainable.