Who Manages the Most People?

A re-org at Kestrel Labs is being planned and the first question in the room is who is already stretched. The measure everyone agrees on is span of control: how many people report directly to each manager today.

The awkward part is that managers are not stored anywhere separately. There is one table of people, and a manager is simply somebody whose id appears in another person's manager_id. So the same table has to play two roles at once — the people being counted, and the people they point at.

employees — one row per person. department_id is the team they sit on, given as the matching id in departments; salary is annual pay in dollars; hire_date is the day they started, written year-month-day; manager_id is the id of the person they report to, and is NULL for anyone who reports to nobody.

The table already exists in the database — there is nothing to create or load.

Task: Write a query returning two columns, manager_name (the manager's name) and reports (how many employees name that person as their manager), one row per person who has at least one direct report. Someone who manages nobody does not appear at all, not even with a zero. Order the rows by reports, largest first, and break ties alphabetically by manager_name.

Example output — shape only, on invented people. The last two are level, so they fall alphabetically: