[MEDIUM] #1076 - Project Employees II

[hackdartstorm]

🎯 Problem: Project Employees II

📖 The Real Problem

You have two tables:

Project table:

• project_id (int)
• employee_id (int)

Employee table:

• employee_id (int)
• name (varchar)
• experience_years (int)

Your task: For each project, calculate the AVERAGE experience years of all employees on that project.

The Challenge:

• Need to JOIN two tables
• Group results by project
• Calculate average with proper rounding (2 decimal places)
• Handle projects with different team sizes

Why this problem exists:

• Real-world analytics: team composition analysis
• Tests SQL JOIN and aggregation skills
• Requires understanding of GROUP BY and AVG()

💡 Why This Matters

Real-world applications:

• Team Analytics - Analyze team experience levels
• Resource Planning - Balance experience across projects
• Budget Planning - Senior teams cost more
• Project Bidding - Estimate based on team expertise

Skills you'll develop:

• ✅ SQL JOIN operations
• ✅ GROUP BY clause
• ✅ AVG() aggregate function
• ✅ ROUND() for decimal precision
• ✅ Data aggregation across tables

📋 Contributor Tasks

Step 1: Understand the Data

1. Examine Project table (project_id, employee_id)
2. Examine Employee table (employee_id, name, experience_years)
3. Understand relationship: Project references Employee via employee_id
4. Goal: Calculate average experience per project

Step 2: Plan Your Approach

1. JOIN Project and Employee tables on employee_id
2. GROUP BY project_id
3. Calculate AVG(experience_years) for each group
4. ROUND to 2 decimal places

Step 3: Implement the Solution

1. Write SELECT with project_id and AVG calculation
2. JOIN Project and Employee
3. GROUP BY project_id
4. Use ROUND(AVG(...), 2) for proper formatting

Step 4: Test Your Solution

1. Test with single employee per project
2. Test with multiple employees
3. Test with varying experience levels
4. Verify rounding works correctly

✅ Expected Outcome

SQL Query Structure:

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

Expected Behavior:

• ✅ Returns one row per project
• ✅ Includes project_id and average_years
• ✅ Average rounded to 2 decimal places
• ✅ All projects included (even with 1 employee)
• ✅ Correct calculation (sum / count)

Example Test Case:

-- 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   | 3                |
-- | 4           | Doe    | 4                |
-- +-------------+--------+------------------+

-- Output:
-- +-------------+---------------+
-- | project_id  | average_years |
-- +-------------+---------------+
-- | 1           | 2.67          |  -- (3+2+3)/3 = 8/3 = 2.666... → 2.67
-- | 2           | 3.50          |  -- (3+4)/2 = 7/2 = 3.5 → 3.50
-- +-------------+---------------+

📚 Additional Context & References

Understanding the Problem

Key Operations:

1. JOIN - Combine Project and Employee data
2. GROUP BY - Aggregate by project
3. AVG() - Calculate mean experience
4. ROUND() - Format to 2 decimals

SQL Order of Execution:

FROM → JOIN → WHERE → GROUP BY → SELECT → ORDER BY

Solution Approach

Standard SQL Solution:

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
ORDER BY p.project_id

Hints (Use Only If Stuck!)

💡 Hint 1

You need to combine data from both tables. What SQL operation does that?

💡 Hint 2

To calculate per-project statistics, you need to group the results by project_id.

💡 Hint 3

Use ROUND(AVG(column), 2) to get the average rounded to 2 decimal places.

Complexity Analysis

Time Complexity: O(n log n)

• JOIN: O(n log n) with indexes
• GROUP BY: O(n log n) for sorting
• AVG calculation: O(n)

Space Complexity: O(n)

• Temporary storage for JOIN results
• Group buckets for aggregation

SQL Functions Used

AVG():

AVG(column) -- Calculates arithmetic mean
-- Example: AVG(3, 2, 3) = 8/3 = 2.666...

ROUND():

ROUND(value, decimals) -- Rounds to specified decimal places
-- Example: ROUND(2.666..., 2) = 2.67

GROUP BY:

GROUP BY column -- Groups rows by column value
-- Used with aggregates: COUNT, SUM, AVG, MIN, MAX

Edge Cases to Consider

1. Empty tables: Return empty result
2. Single employee: Average equals that employee's experience
3. All same experience: Average equals that value
4. NULL values: AVG ignores NULLs (standard SQL behavior)
5. Large numbers: Ensure no overflow in calculation

Related Problems

Once you solve this, try:

• Project Employees I - Basic JOIN
• Project Employees III - Find MAX with ties
• Employee Bonus - Similar JOIN pattern
• Customer Placing the Largest Order - Aggregation

Helpful Resources

• SQL AVG Function
• SQL GROUP BY
• SQL JOIN
• SQL ROUND Function
• LeetCode 1076 - Project Employees II

📝 Notes

• Round to exactly 2 decimal places
• Use standard SQL rounding rules
• Each project appears exactly once in output
• experience_years is guaranteed to be non-NULL
• Output columns: project_id, average_years

---

Ready to contribute?

1. Fork the repository
2. Create your SQL solution file
3. Test with provided examples
4. Submit a pull request!

File Location: exercises/1000_programs/medium/1076_project_employees_ii.sql

🚀 Happy coding!