logo

Back to questions

2nd Level Managers

Hard

Assume we have a table of Google employees with their corresponding managers.

A manager is an employee with a direct report. A 2nd level manager is an employee who manages at least one manager, but none of their direct reports are 2nd level managers themselves. Write a query to find the 2nd level managers and their direct reports.

Output the manager's name and the count of their direct reports. The manager with the most direct reports should be the first result.

Assumptions:

  • An employee can have two second level managers.

Table:

Column NameType
emp_idinteger
manager_idinteger
manager_namestring

Example Input:

emp_idmanager_idmanager_name
1101Duyen
1011001Rick
1031001Rick
10011008John

Example Output:

manager_namedirect_report_count
Rick1

Rick is a second level manager who has one first level manager directly reporting to him, which is employee id 101.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.