570. Managers with at Least 5 Direct Reports / LeetCode, SQL, MS SQL Server

문제

source: LeetCode

Write an SQL query to report the managers with at least five direct reports.
Return the result table in any order.
The query result format is in the following example.

 

 

 

 

제출답안(MS SQL Server)

WITH
    cnt_mng_by_name
        AS
            (SELECT managerId AS id, COUNT(id) AS cnt_sub
            FROM employee
            GROUP BY managerId)

SELECT 
    name
FROM 
    cnt_mng_by_name AS cnt_mng
    JOIN employee AS emp ON cnt_mng.id = emp.id
WHERE 
    cnt_sub >= 5

 

 

문제 바로가기(MS SQL Server)