1075. Project Employees I
On LeetCode ->Problem¶
Given project-employee assignments and employee experience, return each project with the average experience of its assigned employees, rounded to 2 decimals.
Input:
# Project
-----------+------------
project_id | employee_id
-----------+------------
1 | 1
1 | 2
1 | 3
2 | 1
2 | 4
# Employee
------------+--------+-----------------
employee_id | name | experience_years
------------+--------+-----------------
1 | Khaled | 3
2 | Ali | 2
3 | John | 1
4 | Doe | 2
Output:
Key trick¶
Join assignments to employees, then aggregate by project_id.
Trap¶
- Round the final average, not each employee's years before averaging.
- Group only by
project_id. - Do not use
COUNT(project_id)with manual division unless you handle types correctly. - In Pandas, remember to return
project_idas a column, not as the index.
Why is it interesting?¶
It tests the basic SQL interview pattern: join, group, aggregate, and format the aggregate result.
SQL solution¶
-- SQLite
SELECT
p.project_id,
ROUND(AVG(e.experience_years), 2) AS average_years
FROM Project AS p
JOIN Employee AS e
ON e.employee_id = p.employee_id
GROUP BY
p.project_id;
Pandas solution¶
import pandas as pd
def project_employees_i(project: pd.DataFrame, employee: pd.DataFrame) -> pd.DataFrame:
# Join assignments with employee experience.
merged = project.merge(employee, on="employee_id", how="inner")
# Average experience per project, then round the final average.
result = (
merged.groupby("project_id", as_index=False)
.agg(average_years=("experience_years", "mean"))
)
result["average_years"] = result["average_years"].round(2)
return result[["project_id", "average_years"]]
Pytest test¶
import sqlite3
import pandas as pd
import pytest
SQL_QUERY = """
SELECT
p.project_id,
ROUND(AVG(e.experience_years), 2) AS average_years
FROM Project AS p
JOIN Employee AS e
ON e.employee_id = p.employee_id
GROUP BY
p.project_id;
"""
def pandas_solution(project: pd.DataFrame, employee: pd.DataFrame) -> pd.DataFrame:
result = (
project.merge(employee, on="employee_id", how="inner")
.groupby("project_id", as_index=False)
.agg(average_years=("experience_years", "mean"))
)
result["average_years"] = result["average_years"].round(2)
return result[["project_id", "average_years"]]
def sql_solution(project: pd.DataFrame, employee: pd.DataFrame) -> pd.DataFrame:
with sqlite3.connect(":memory:") as conn:
project.to_sql("Project", conn, index=False, if_exists="replace")
employee.to_sql("Employee", conn, index=False, if_exists="replace")
return pd.read_sql_query(SQL_QUERY, conn)
def normalize(df: pd.DataFrame) -> pd.DataFrame:
out = df.sort_values("project_id").reset_index(drop=True)
out["project_id"] = out["project_id"].astype("int64")
out["average_years"] = out["average_years"].astype("float64").round(2)
return out
@pytest.mark.parametrize(
"project_rows, employee_rows, expected_rows",
[
(
[[1, 1], [1, 2], [1, 3], [2, 1], [2, 4]],
[[1, "Khaled", 3], [2, "Ali", 2], [3, "John", 1], [4, "Doe", 2]],
[[1, 2.00], [2, 2.50]],
),
(
[[10, 1]],
[[1, "Alice", 7]],
[[10, 7.00]],
),
(
[[1, 1], [1, 2], [1, 3], [2, 2], [2, 3]],
[[1, "A", 1], [2, "B", 2], [3, "C", 5]],
[[1, 2.67], [2, 3.50]],
),
],
)
def test_project_employees_i(project_rows, employee_rows, expected_rows):
project = pd.DataFrame(project_rows, columns=["project_id", "employee_id"])
employee = pd.DataFrame(
employee_rows,
columns=["employee_id", "name", "experience_years"],
)
expected = pd.DataFrame(expected_rows, columns=["project_id", "average_years"])
sql_result = sql_solution(project, employee)
pandas_result = pandas_solution(project, employee)
pd.testing.assert_frame_equal(normalize(sql_result), normalize(expected))
pd.testing.assert_frame_equal(normalize(pandas_result), normalize(expected))
Comment on my solution¶
Your SQL solution is correct.
LEFT JOINis harmless becauseProject.employee_idis guaranteed to referenceEmployee.JOINwould be slightly clearer because every project assignment must have a matching employee.
Your Pandas solution is also correct.
- It joins, groups, averages, rounds after aggregation, and returns the expected columns.
- The only minor improvement is that
how="inner"better matches the foreign-key guarantee, buthow="left"still works here.
SELECT
p.project_id,
ROUND(AVG(experience_years), 2) AS average_years
FROM Project AS p
LEFT JOIN Employee AS e
ON e.employee_id = p.employee_id
GROUP BY p.project_id;
import pandas as pd
def project_employees_i(project: pd.DataFrame, employee: pd.DataFrame) -> pd.DataFrame:
result = (
project.merge(employee, on="employee_id", how="left")
.groupby("project_id", as_index=False)
.agg(average_years=("experience_years", "mean"))
[["project_id", "average_years"]]
)
result["average_years"] = result["average_years"].round(2)
return result
Extra¶
JOIN vs. LEFT JOIN and how="left" vs. how="inner"¶
Short answer¶
For this problem, JOIN and LEFT JOIN give the same result because the schema guarantees:
- Every
Project.employee_idexists inEmployee. Employee.experience_yearsis notNULL.
So there are no unmatched rows and no missing experience values.
JOIN is preferred because it says exactly what we mean:
- Keep project assignments that have a valid employee.
- The foreign key guarantees that all assignments are valid.
In this example¶
Given:
Project
project_id | employee_id
-----------+------------
1 | 1
1 | 2
Employee
employee_id | experience_years
------------+-----------------
1 | 3
2 | 5
Both queries produce the same joined rows:
project_id | employee_id | experience_years
-----------+-------------+-----------------
1 | 1 | 3
1 | 2 | 5
So both averages are:
What if an employee is missing?¶
Suppose the data were broken:
Project
project_id | employee_id
-----------+------------
1 | 1
1 | 99
Employee
employee_id | experience_years
------------+-----------------
1 | 3
With JOIN:
Average:
With LEFT JOIN:
project_id | employee_id | experience_years
-----------+-------------+-----------------
1 | 1 | 3
1 | 99 | NULL
AVG ignores NULL, so the average is still:
This is dangerous because the missing employee is silently ignored.
Why LEFT JOIN can hide bugs¶
This query:
SELECT
p.project_id,
ROUND(AVG(e.experience_years), 2) AS average_years
FROM Project AS p
LEFT JOIN Employee AS e
ON e.employee_id = p.employee_id
GROUP BY p.project_id;
can make invalid data look valid.
If one employee is missing, the project still appears, and AVG skips the missing value.
That means this broken input:
project_id | employee_id | experience_years
-----------+-------------+-----------------
1 | 1 | 3
1 | 99 | NULL
returns:
But maybe the real answer should be considered invalid because employee 99 does not exist.
Pandas equivalent¶
how="inner":
Keeps only rows with matching employees.
how="left":
Keeps every project assignment, even if employee data is missing.
If an employee is missing, Pandas gives NaN:
project_id | employee_id | experience_years
-----------+-------------+-----------------
1 | 1 | 3
1 | 99 | NaN
Then:
skips NaN, just like SQL AVG skips NULL.
So how="left" can also hide missing employee data.
General rule¶
Use INNER JOIN when:
- You only want rows that have matches on both sides.
- The relationship is required.
- A foreign key guarantees the match exists.
- Missing matches would indicate bad data.
Use LEFT JOIN when:
- You want to preserve all rows from the left table.
- Missing right-side data is allowed.
- You want to show rows even when related data does not exist.
- You are intentionally producing
NULLorNaNfor missing matches.
Example where LEFT JOIN is correct¶
If you have a real Projects table and want all projects, including projects with no employees:
Projects
project_id
----------
1
2
3
ProjectEmployee
project_id | employee_id
-----------+------------
1 | 1
2 | 2
Then LEFT JOIN from Projects is correct because project 3 should still appear.
Output might be:
For this LeetCode problem¶
JOIN is slightly better because:
- The foreign key guarantees every employee exists.
- The problem asks for averages of assigned employees.
- There is no need to preserve unmatched assignments.
- It communicates the intended relationship more clearly.
Your LEFT JOIN answer is still accepted and correct for the given constraints.