Back to questions
Senior Managers Google SQL Interview Question
Senior Managers
Google SQL Interview Question
Assume we have a table of Google employees with their corresponding managers.
A manager is an employee with a direct report. A senior manager is an employee who manages at least one manager, but none of their direct reports is senior managers themselves. Write a query to find the senior managers and their direct reports.
Output the senior manager's name and the count of their direct reports. The senior manager with the most direct reports should be the first result.
Assumption:
- An employee can report to two senior managers.
Table:
| Column Name | Type |
|---|---|
| emp_id | integer |
| manager_id | integer |
| manager_name | string |
Example Input:
| emp_id | manager_id | manager_name |
|---|---|---|
| 1 | 101 | Duyen |
| 101 | 1001 | Rick |
| 103 | 1001 | Rick |
| 1001 | 1008 | John |
Example Output:
| manager_name | direct_reportees |
|---|---|
| Rick | 1 |
Rick is a senior manager who has one manager directly reporting to him, which is employee id 101.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Official solution was inspired by @Mohit Khanna and @zotto98.
Imagine a hierarchy of an employee, followed by the manager, followed by the senior manager at the highest level.
The question is asking for the senior manager with their direct reportees which are the manager-level in the hierarchy.
Step 1: Self-join the employee tables twice for managers and senior managers
The joined table is aliased as as it will represent the managers' table.
Next, we will self-join with the employees’ table again and alias as to represent the senior managers' table.
Ensure that the matching criteria in each join are correctly declared:
- In the first , the employees' is matched to the managers' to create a table where the employee is also a manager.
- In the second , the managers' is matched to the senior managers' to retrieve a refined table where the manager is also a senior manager.
| emp_id | manager_id | manager_name | emp_id | manager_id | manager_name | emp_id | manager_id | manager_name |
|---|---|---|---|---|---|---|---|---|
| 1 | 101 | Duyen | 101 | 1001 | Rick | 1001 | ||
| 8 | 107 | Tina | 107 | 1004 | Tom | 1004 | 1014 | Brandon |
| 8 | 107 | Tina | 107 | 1004 | Tom | 1004 | 1002 | Jason |
| 12 | 150 | Raman | 150 | 1001 | Rick | 1001 |
From L-R: The first is the manager, the second is the senior manager and the third is the director.
Step 2: Output the senior manager and direct reportees
All we have to do now is:
- Define the desired output: Senior manager's name and direct reportees
- Order the output by direct reportees in descending order.