[리트코드] 1731. The Number of Employees Which Report to Each Employee - 조인

 

# 문제

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

 

# 문제요약

 

report_to의 employee_id 별로 이름 출력하고, 총 몇명이 reports_to로 하였는지, 평균 연령도 출력

 

# 핵심 개념

 

1) with

2) 조인✔️

3) count

4) 윈도우함수

 

Employees 테이블의 reports_to와 Employees 테이블의 employee_id를 매칭시켜야 하므로 inner join을 시켜준다

 

group by employee_id, name 별로 집계함수를 사용하였다

 

 

 

# 정답 코드

select s.employee_id, 
       s.name,
       count(*) as reports_count,
       round(avg(e.age)) as average_age
from Employees e
inner join Employees s
on e.reports_to = s.employee_id
group by s.employee_id, s.name
order by s.employee_id