2nd Level Managers
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.
Rick is a second level manager who has one first level manager directly reporting to him, which is employee id 101.
This question is quite complicated to execute in a relational database, so let's go through the overarching steps we need to execute for this problem:
See how complicated this seemingly simple task becomes? Let's do it together, step-by-step.
Step 1: 2nd-Level Manager Data
For this step, we need to pull the 1st-level manager ID along with the 2nd-level manager's ID and name. Very simply:
This seems straightforward enough, but the trick here is to get the filters right. Most importantly, the only employees we want to to look at need to be managers themselves. We can do this by using a quick subquery to ensure that all the employee IDs we pull are also managers: Click here to learn more about subqueries in SQL.
We also want to make sure that each employee ID has a manager, to ensure that they are first-level, and not too high up in the company. For example, the CEO wouldn't have a manager, and we'd want them to be excluded from this analysis. We can filter out employees that don't have managers using the and fields.
Putting it all together, our query looks like this:
The first 3 rows of output should be:
Step 2: Complex Filtering
Let's create each filter as a short subquery, and then make sure that these results are excluded from our data.
The first thing we need to avoid is accidentally putting second level managers in the first level manager column. We can pull all of the results we want to avoid with this subquery:
The next thing we need to avoid is including second level managers who themselves have second level managers as direct reports . We can pull all of the results we want to avoid with this subquery:
Now, we'll use to exclude these filter sections from our output. Click here to learn more about using and for filtering in SQL.
After wrapping the previous query in a Common Table Expression (CTE), we have:
Step 3: Final Aggregations
Now, all the hard work is done. We'll wrap the above query in a second CTE and write the main statement.
First, we'll select the second level manager names along with a count of the first level managers – the direct reports. We'll also put the results in descending order based on the count of the first level managers, as specified in the question. Then, we'll group by the manager name, and that's it!
Here are the final results:
Great job – you powered through a really tough question. This is what learning is all about :) Keep it up!