Project Employees I - Practice Coding Problems

Project Employees I - Problem

Database Easy

Project Employees I

You're working as a data analyst at a tech consulting company that manages multiple projects with different teams. Your task is to calculate the average experience level of employees working on each project to help with resource allocation and project planning.

You have access to two database tables:

Project table shows which employees are assigned to which projects:

project_id	employee_id

Employee table contains employee details including their years of experience:

employee_id	name	experience_years

Goal: Write an SQL query that calculates the average experience years of all employees for each project, rounded to 2 decimal places.

Key Requirements:
• Join the two tables using employee_id
• Group results by project_id
• Calculate average and round to 2 decimal places
• Return results in any order

Input & Output

example_1.sql — Basic Example

$ 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 |

💡 Note: Project 1 has employees with 3, 2, and 1 years of experience. Average = (3+2+1)/3 = 2.00. Project 2 has employees with 3 and 2 years of experience. Average = (3+2)/2 = 2.50.

example_2.sql — Single Employee Projects

$ Input: Project table: | project_id | employee_id | |------------|-------------| | 1 | 1 | | 2 | 2 | | 3 | 3 | Employee table: | employee_id | name | experience_years | |-------------|---------|------------------| | 1 | Alice | 5 | | 2 | Bob | 10 | | 3 | Charlie | 7 |

› Output: | project_id | average_years | |------------|---------------| | 1 | 5.00 | | 2 | 10.00 | | 3 | 7.00 |

💡 Note: Each project has only one employee, so the average experience equals that employee's experience years, rounded to 2 decimal places.

example_3.sql — Rounding Edge Case

$ Input: Project table: | project_id | employee_id | |------------|-------------| | 1 | 1 | | 1 | 2 | | 1 | 3 | Employee table: | employee_id | name | experience_years | |-------------|-------|------------------| | 1 | Tom | 2 | | 2 | Jerry | 3 | | 3 | Mike | 4 |

› Output: | project_id | average_years | |------------|---------------| | 1 | 3.00 |

💡 Note: Project 1 has employees with 2, 3, and 4 years of experience. Average = (2+3+4)/3 = 3.00 exactly, demonstrating proper rounding to 2 decimal places.

Visualization

Tap to expand

Understanding the Visualization

JOIN Operation

Connect Project assignments with Employee details using employee_id as the key

GROUP BY Project

Organize the joined data by project_id to create groups of employees per project

Calculate AVG

Apply the AVG() function to experience_years within each project group

Round Results

Use ROUND() function to format averages to exactly 2 decimal places

Key Takeaway

🎯 Key Insight: SQL's JOIN and GROUP BY operations work together perfectly for aggregating related data - the database engine optimizes this pattern for maximum performance!

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Where n is projects and m is employees - single pass through joined data

✓ Linear Growth

Space Complexity

O(p)

Where p is number of projects for grouping intermediate results

✓ Linear Space

Constraints

1 ≤ project_id, employee_id ≤ 100
0 ≤ experience_years ≤ 50
experience_years is guaranteed to be NOT NULL
Each employee can work on multiple projects
Each project must have at least one employee

Asked in

a Amazon 45 ⊞ Microsoft 38 G Google 32 f Meta 28

The optimal solution uses INNER JOIN to combine the Project and Employee tables, then applies GROUP BY project_id with AVG(experience_years) to calculate average experience per project. The ROUND() function ensures results are rounded to 2 decimal places. This approach is efficient with O(n + m) time complexity and leverages SQL's built-in aggregation capabilities.

Common Approaches

Approach	Time	Space	Notes
✓ JOIN with GROUP BY (Optimal)	O(n + m)	O(p)	Use INNER JOIN and GROUP BY to efficiently calculate averages in one pass
Naive Subquery Approach	O(n × m)	O(1)	Use subqueries for each project to calculate average separately

JOIN with GROUP BY (Optimal) — Algorithm Steps

INNER JOIN Project and Employee tables on employee_id
GROUP BY project_id to group employees by project
Apply AVG() function to experience_years column
Use ROUND() function to round to 2 decimal places
SELECT project_id and calculated average

Visualization

Tap to expand

Step-by-Step Walkthrough

JOIN Tables

Combine Project and Employee tables on employee_id

GROUP BY Project

Group the joined results by project_id

Calculate AVG

Apply AVG function to experience_years in each group

ROUND Result

Round averages to 2 decimal places

Code -

solution.c — C

// Optimal SQL query using JOIN and GROUP BY
char query[] = "\
SELECT \
    p.project_id, \
    ROUND(AVG(e.experience_years), 2) as average_years \
FROM Project p \
INNER JOIN Employee e ON p.employee_id = e.employee_id \
GROUP BY p.project_id;"

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Where n is projects and m is employees - single pass through joined data

✓ Linear Growth

Space Complexity

O(p)

Where p is number of projects for grouping intermediate results

✓ Linear Space

Constraints

1 ≤ project_id, employee_id ≤ 100
0 ≤ experience_years ≤ 50
experience_years is guaranteed to be NOT NULL
Each employee can work on multiple projects
Each project must have at least one employee

23.5K Views

High Frequency

~12 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

JOIN with GROUP BY (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler