SQL문제풀기

[SQL 문제 풀이] The Number of Employees Which Report to Each Employee (각 직원에게 보고하는 직원 수)

Jun 30, 2025

[SQL 문제 풀이] The Number of Employees Which Report to Each Employee (각 직원에게 보고하는 직원 수)

Contents

내가 작성한 쿼리

The Number of Employees Which Report to Each Employee - LeetCode

Can you solve this real interview question? 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 | +-------------+---------+---------------+-------------+

The Number of Employees Which Report to Each Employee - LeetCode

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

The Number of Employees Which Report to Each Employee - LeetCode

notion image


이 문제에 대해 우리는 관리자를 최소 한 명의 다른 직원이 보고하는 직원으로 간주할 것입니다.

ID와 모든 관리자의 이름, 직접 보고하는 직원 수,
보고서의 평균 연령을 가장 가까운 정수로 반올림하여 보고하는 솔루션을 작성하세요.

employee_id를 기준으로 정렬한 결과 테이블을 반환합니다.

내가 작성한 쿼리

MySQL


SELECT
    e1.employee_id,
    e1.name,
    COUNT(*) AS reports_count,
    ROUND(AVG(e2.age)) AS average_age
FROM Employees e1 
JOIN Employees e2 
    ON e1.employee_id = e2.reports_to
GROUP BY e1.employee_id
ORDER BY e1.employee_id;

Oracle


SELECT
    e1.employee_id,
    e1.name,
    COUNT(*) AS reports_count,
    ROUND(AVG(e2.age)) AS average_age
FROM Employees e1 
JOIN Employees e2 
    ON e1.employee_id = e2.reports_to
GROUP BY e1.employee_id, e1.name
ORDER BY e1.employee_id;

차이점

항목	MySQL	Oracle
`GROUP BY` 대상	`e1.employee_id`만 있어도 OK	`SELECT`에 있는 모든 비집계 컬럼 필요 (`e1.employee_id`, `e1.name`)

Share article

More articles

[JAVA 문제 풀이] 352. 쿼드압축 후 개수 세기

[JAVA 문제 풀이] 352. 쿼드압축 후 개수 세기

[SQL 문제 풀이] Managers with at Least 5 Direct Reports (5개 이상의 직접 보고서를 보유한 관리자)

[SQL 문제 풀이] Managers with at Least 5 Direct Reports (5개 이상의 직접 보고서를 보유한 관리자)

[JAVA 문제 풀이] 351. 2개 이하로 다른 비트

[JAVA 문제 풀이] 351. 2개 이하로 다른 비트

[JAVA 문제 풀이] 350. 프렌즈4블록

[JAVA 문제 풀이] 350. 프렌즈4블록

stupefyee

RSS·Powered by Inblog