# The Roster > Every department, counted head by head. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L5 ## Problem We need a headcount breakdown by department, from the biggest teams down to the smallest. When two departments have the same headcount, list them in alphabetical order. ## Worked solution and explanation ### What this really tests This looks like a throwaway headcount, and the aggregation is: one group per department, count the rows. What actually separates answers is the tie-break. On this data the departments land at nearly the same size, so ordering on the count alone leaves the rows in whatever order the engine feels like emitting, and your output stops matching the expected result row for row. The skill being probed is whether you make the ordering total, not merely descending. --- ### Building the answer #### Step 1: Collapse to one row per department `GROUP BY department` collapses the table to one row per department. The other six columns (name, salary, manager, and so on) are along for the ride and play no part here. #### Step 2: Count, then order on two keys `COUNT(*)` is the headcount. Sort it descending so the biggest teams come first, then add `department ASC` as a second key so equal counts fall into a fixed, alphabetical order. --- ### The solution **GROUP BY with a deterministic sort** ```sql SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department ORDER BY employee_count DESC, department ASC ``` > **The tie-break is the point** > > The whole problem hinges on the second sort key. `ORDER BY employee_count DESC` alone is not a total order when counts tie, and the grader compares your rows in order. `department ASC` is what makes the result reproducible. > **Interviewers watch for** > > A candidate who writes only `ORDER BY employee_count DESC` and moves on has not noticed that ties exist. The ones who add the second key without being told signal that they think about determinism, not just correctness. > **Common pitfall** > > Leaning on the engine's incidental row order for ties. It works on your laptop, then a version bump or a parallel scan reorders the tied rows and a downstream diff lights up red. > **Cost analysis** > > One hash aggregate over the table. With an index on `department` the planner can satisfy the grouping from the index, so even at eighty thousand rows this stays a single cheap pass. --- ## Common follow-up questions - How would you include departments that currently have zero employees? _(Tests LEFT JOIN from a departments reference table.)_ - What if you only wanted the three largest departments? _(Tests LIMIT usage.)_ - How does COUNT(*) differ from COUNT(manager_id) on this table? _(Tests NULL handling in aggregates.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_roster) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.