Each Employee's Direct-Report Count
For every employee, count how many people report DIRECTLY to them (0 if none). Return manager_id (the employee's id), manager_name, total_reports — ordered by total_reports DESC, manager_id.
- Joins
- Aggregation
- Sorting
Exercise brief
Understand the request
Org design analyst The span-of-control scorecard must show every employee, including individual contributors with zero reports.
Return
- Return manager ID, manager name, and direct-report count.
- Order by count descending and manager_id.
Constraints
- Preserve the manager side with LEFT JOIN.
- Count the nullable matched employee key, not COUNT(*).
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
employee_idINTEGERemployee_nameTEXTmanager_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Use COUNT(e.employee_id), not COUNT(*) — COUNT(*) returns 1 for everyone (LEFT JOIN emits a NULL-padded row).
Hint 2
GROUP BY both id AND name (every non-aggregated SELECT column).
Hint 3
Result includes ICs (zero reports). To restrict to managers only, add `HAVING COUNT(e.employee_id) > 0`.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT m.employee_id AS manager_id, m.employee_name AS manager_name, COUNT(e.employee_id) AS total_reports FROM employees m LEFT JOIN employees e ON m.employee_id = e.manager_id GROUP BY m.employee_id, m.employee_name ORDER BY total_reports DESC, m.employee_id;Why this works
COUNT(col) ignores NULLs, so empty groups correctly come out as 0. This is the canonical 'subordinates per employee' query — useful for org-chart sizing, span-of-control analysis, and IC-vs-manager dashboards.
Success check
All employees appear once and leaf employees have a zero report count.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| manager_id | manager_name | total_reports |
|---|---|---|
| 5 | Emma Sales Mgr | 4 |
| 1 | Alice CEO | 3 |
| 3 | Carol VP Eng | 2 |
| 6 | Frank Eng Mgr | 2 |
| 2 | Bob VP Sales | 1 |
| 4 | David VP HR | 1 |
| 7 | Grace HR Mgr | 1 |
| 8 | Henry Sales Rep | 0 |
| 9 | Ivy Sales Rep | 0 |
| 10 | Jack Engineer | 0 |
Previewing 10 of 15 expected rows. Run the query in the editor to inspect the full result.
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SQL Joins
Practice reliable INNER, LEFT, FULL, CROSS, self, semi, anti, range, temporal, and many-to-many join patterns.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
SQL Subqueries
Practice scalar, derived-table, correlated, EXISTS, NULL-safe anti-subquery, quantified, and row-subquery patterns.
Open the interactive workspace and practice across SQL topics.