Headcount by Department Name

The board deck for Kestrel Labs has a slide showing how the company is distributed across its teams, and the version that came back from review has a note on it in red: nobody outside this room knows what team 4 is. The slide has to name the teams.

That is the difficulty here rather than the counting. People are counted in one table, and team names live in another; the employee row knows only a number. The deck wants the biggest team at the top, and three of the four teams are the same size, so the slide also needs a rule for what happens when sizes are equal.

departments — one row per team. budget is that team's annual budget in dollars.

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.

Both tables already exist in the database — there is nothing to create or load.

Task: Write a query returning two columns, name (the team's name) and headcount (how many employees are on that team), one row per team, largest headcount first. When two teams have the same headcount, list them alphabetically by name. A team with nobody on it would not appear at all; every team here has at least one employee, so all four are in the result.

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