Rank Aggregated Department Headcount
Return department_id and headcount, grouped by department and ordered by headcount descending, then department_id ascending.
- Aggregation
- Sorting
Exercise brief
Understand the request
Workforce analytics manager A capacity summary ranks departments by headcount and needs stable ordering when counts tie.
For each department_id, return the headcount, sorted by headcount descending then department_id ascending. Show department_id and headcount.
Return
- Return one row per department.
- Order larger headcounts first and resolve ties by department_id.
Constraints
- Use GROUP BY department_id.
- Sort by the aggregate alias and a deterministic tie-breaker.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
department_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
After GROUP BY, ORDER BY operates on the grouped rows. You can sort by any GROUP BY column or any aggregate.
Hint 2
Most engines accept the alias (`headcount`) in ORDER BY; the strict-portable form repeats the aggregate: `ORDER BY COUNT(*) DESC`.
Hint 3
ORDER BY headcount DESC, department_id
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT department_id, COUNT(*) AS headcount FROM employees GROUP BY department_id ORDER BY headcount DESC, department_id;Why this works
Sorting by aggregates is the foundation of "Top departments by X" reports. The same idea pairs with LIMIT for "Top 5 …", with HAVING for "departments with at least N people, sorted by Y", and with ranking window functions for "rank within each group".
Success check
The grouped counts and their deterministic ranking both match.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| department_id | headcount |
|---|---|
| 1 | 5 |
| 3 | 4 |
| 2 | 3 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
LIMIT & OFFSET
Practice deterministic top-N, cutoff ties, offset pagination, composite keyset cursors, and resumable bounded batches.
Ranking & NTH Value
Solve deterministic ranking, top-N, distribution, positional-frame, and rolling-window problems.
SELECT Statements
Select columns, filter rows, remove duplicates, and order query results.
Open the interactive workspace and practice across SQL topics.