Atleast5DirectReports.sql

-- Table: Employee

-- +-------------+---------+
-- | Column Name | Type    |
-- +-------------+---------+
-- | id          | int     |
-- | name        | varchar |
-- | department  | varchar |
-- | managerId   | int     |
-- +-------------+---------+
-- id is the primary key (column with unique values) for this table.
-- Each row of this table indicates the name of an employee, their department, and the id of their manager.
-- If managerId is null, then the employee does not have a manager.
-- No employee will be the manager of themself.
 

-- Write a solution to find managers with at least five direct reports.

-- Return the result table in any order.

-- The result format is in the following example.

 

-- Example 1:

-- Input: 
-- Employee table:
-- +-----+-------+------------+-----------+
-- | id  | name  | department | managerId |
-- +-----+-------+------------+-----------+
-- | 101 | John  | A          | null      |
-- | 102 | Dan   | A          | 101       |
-- | 103 | James | A          | 101       |
-- | 104 | Amy   | A          | 101       |
-- | 105 | Anne  | A          | 101       |
-- | 106 | Ron   | B          | 101       |
-- +-----+-------+------------+-----------+
-- Output: 
-- +------+
-- | name |
-- +------+
-- | John |
-- +------+

-- Solution
-- Write your PostgreSQL query statement below
select EmployeeName as name from 
(
    select EmployeeId,
       EmployeeName,
       count(Employee_managerId) as count_Employee_managerId
from (
    select Employee.id as EmployeeId,
        Employee.name as EmployeeName,
       emp.managerId as Employee_managerId
    from Employee 
        join Employee emp 
        on (Employee.id = emp.managerId)
    where emp.managerId is not null
)
group by EmployeeId,
       EmployeeName
)
where count_Employee_managerId >= 5