Level 7 · Team size per manager

A self-join reads one table twice under two aliases. In employees, every row is a person and manager_id points to another row of the same table. Giving the table two names lets you treat one copy as “the report” and the other as “the manager”.

Recap: two roles, one table

SELECT e.first_name || ' ' || e.last_name AS employee_name,
       e.department,
       m.first_name || ' ' || m.last_name AS manager_name,
       m.department AS manager_department
FROM employees AS e
JOIN employees AS m ON m.employee_id = e.manager_id
WHERE e.department <> m.department;

This lists the people whose manager sits in another department: 8 rows, all department heads reporting to Catherine Hale or Robert Kim in Executive. The direction of the join condition carries the meaning: m.employee_id = e.manager_id reads “m is the manager of e”.

For the ticket, the same join is followed by a GROUP BY on the manager. After the join there is one row per report, so counting rows per manager counts their direct reports. The inner join drops anyone with no reports, which is exactly the “at least one direct report” rule. Only 12 of the 60 employees manage someone. If HR also wanted the people who manage nobody, with 0 reports, you would start from the manager copy and LEFT JOIN the reports instead.

Group by m.employee_id as well as the name columns. Two managers could share a name; the id keeps them apart even when you only display the name.

Common mistakes

• Reversed condition. ON m.manager_id = e.employee_id makes m the report and e the manager. The query still runs and returns plausible but wrong teams.
• Averaging the wrong alias. AVG(m.salary) is the manager’s own salary repeated on every row.
• Direct versus all reports. This counts direct reports only. Someone reporting to a team lead who reports to a head is not counted for the head. Walking the whole chain needs a recursive query, which appears later in the track.

In SQL Server

String concatenation uses + or CONCAT: m.first_name + ' ' + m.last_name AS manager_name. CONCAT also treats NULL as an empty string, while + returns NULL if any part is NULL. || exists only in the newest versions (Azure SQL, SQL Server 2025), so do not count on it.