Project Employees I Problem


Description

LeetCode Problem 1075.

Table: Project

1
2
3
4
5
6
7
8
+-------------+---------+
| 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.

Table: Employee

1
2
3
4
5
6
7
8
+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| employee_id      | int     |
| name             | varchar |
| experience_years | int     |
+------------------+---------+
employee_id is the primary key of this table.

Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits.

The query result format is in the following example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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                |
+-------------+--------+------------------+

Result table:
+-------------+---------------+
| project_id  | average_years |
+-------------+---------------+
| 1           | 2.00          |
| 2           | 2.50          |
+-------------+---------------+
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


MySQL Solution

1
2
3
4
5
6
select p.project_id,
    round(sum(e.experience_years)/count(p.project_id), 2) as average_years
from Project p 
left join Employee e
on p.employee_id = e.employee_id
group by p.project_id




Related Posts

Product Sales Analysis III Problem

LeetCode 1070. Write an SQL query that selects the product...

Project Employees III Problem

LeetCode 1077. Write an SQL query that reports the most...

Project Employees II Problem

LeetCode 1076. Write an SQL query that reports all the...

Project Employees I Problem

LeetCode 1075. Write an SQL query that reports the average...

Product Sales Analysis II Problem

LeetCode 1069. Write an SQL query that reports the total...

Product Sales Analysis I Problem

LeetCode 1068. Write an SQL query that reports all product...