Retrieve the name of the manager who supervises the most employees

Company: BlackRock

CTC: 26LPA

Source: Linked Post

Q. Retrieve the name of the manager who supervises the most employees.

Solution:

SELECT manager_id, count(*) as num_emp
FROM employees1
GROUP BY manager_id
ORDER BY num_emp desc
LIMIT 1;

Explanation:

Here, we want to find out the manager who supervises the most employees. To achieve this, the query works in the following steps:

1. Counting Employees for Each Manager:
   • We are using COUNT(*) to count how many employees each manager supervises.
   • COUNT(*) counts all rows in the employees table for each manager_id, meaning it will count how many employees each manager is overseeing.
2. Grouping by Manager:
   • GROUP BY manager_id ensures that the data is grouped by each manager. So, for each manager_id, we will get a count of how many employees report to that manager.
3. Sorting the Results:
   • ORDER BY num_employees DESC sorts the result by the number of employees each manager supervises, in descending order. This will put the manager with the highest number of employees at the top of the list.
4. Limiting the Result:
   • LIMIT 1 ensures that we only get the top result, i.e., the manager who supervises the most employees.

Key Logic:

• The manager_id and num_employees are returned, showing the manager with the highest number of employees under them.
• By using ORDER BY in descending order and limiting the result to just 1, the query ensures we get only the top manager.

Summary:

The query counts how many employees each manager supervises, orders them by the count, and returns the manager with the highest number of employees.