[LeetCode] 1731. The Number of Employees Which Report to Each Employee - SQL

링크: https://leetcode.com/problems/the-number-of-employees-which-report-to-each-employee/

Table: Employees

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| employee_id | int      |
| name        | varchar  |
| reports_to  | int      |
| age         | int      |
+-------------+----------+
employee_id is the column with unique values for this table.
This table contains information about the employees and the id of the manager they report to. Some employees do not report to anyone (reports_to is null).

For this problem, we will consider a manager an employee who has at least 1 other employee reporting to them.

Write a solution to report the ids and the names of all managers, the number of employees who report directly to them, and the average age of the reports rounded to the nearest integer.

Return the result table ordered by employee_id.

The result format is in the following example.

Example 1:

Input:
Employees table:
+-------------+---------+------------+-----+
| employee_id | name    | reports_to | age |
+-------------+---------+------------+-----+
| 9           | Hercy   | null       | 43  |
| 6           | Alice   | 9          | 41  |
| 4           | Bob     | 9          | 36  |
| 2           | Winston | null       | 37  |
+-------------+---------+------------+-----+
Output:
+-------------+-------+---------------+-------------+
| employee_id | name  | reports_count | average_age |
+-------------+-------+---------------+-------------+
| 9           | Hercy | 2             | 39          |
+-------------+-------+---------------+-------------+
Explanation: Hercy has 2 people report directly to him, Alice and Bob. Their average age is (41+36)/2 = 38.5, which is 39 after rounding it to the nearest integer.

Example 2:

Input:
Employees table:
+-------------+---------+------------+-----+
| employee_id | name    | reports_to | age |
|-------------|---------|------------|-----|
| 1           | Michael | null       | 45  |
| 2           | Alice   | 1          | 38  |
| 3           | Bob     | 1          | 42  |
| 4           | Charlie | 2          | 34  |
| 5           | David   | 2          | 40  |
| 6           | Eve     | 3          | 37  |
| 7           | Frank   | null       | 50  |
| 8           | Grace   | null       | 48  |
+-------------+---------+------------+-----+
Output:
+-------------+---------+---------------+-------------+
| employee_id | name    | reports_count | average_age |
| ----------- | ------- | ------------- | ----------- |
| 1           | Michael | 2             | 40          |
| 2           | Alice   | 2             | 37          |
| 3           | Bob     | 1             | 37          |
+-------------+---------+---------------+-------------+

---

문제풀이

목표: 모든 관리자의 id, 이름, 그들에게 직접 보고하는 직원의 수, 평균 연령을 가장 가까운 정수로 반올림하여 보고하는 솔루션 작성

• 관리자 : 자신에게 보고하는 다른 직원이 1명 이상 있는 직원.
• 결과는 employee_id를 기준으로 정렬.

최종코드

SELECT
    mgr.employee_id,
    mgr.name,
    COUNT(emp.employee_id) as reports_count,
    ROUND(AVG(emp.age)) as average_age
FROM Employees emp
    JOIN Employees mgr
    ON emp.reports_to = mgr.employee_id
GROUP BY employee_id
ORDER BY employee_id;

설명

FROM, GROUP BY 절

FROM Employees emp
    JOIN Employees mgr
    ON emp.reports_to = mgr.employee_id
GROUP BY employee_id

• 매니저와 직원들을 셀프 조인으로 나눠준다.
  • 이 때 ON emp.reports_to = mgr.employee_id 를 통해 특정 매니저가 직원에게 보고받는 관계를 만든다.
• 이후 employee_id를 기준으로 그룹화

SELECT 절

SELECT
    mgr.employee_id,
    mgr.name,
    COUNT(emp.employee_id) as reports_count,
    ROUND(AVG(emp.age)) as average_age

• 매니저의 id
• 매니저의 name 조회,
• 직원 테이블에서 직원의 수를 COUNT
• 직원 테이블에서 직원의 평균 나이를 ROUND 함수로 반올림

ORDER BY 절

ORDER BY employee_id

• 마지막으로 employee_id 를 기준으로 정렬.