# Employees Per Department

> Headcount, location by location.

Canonical URL: <https://datadriven.io/problems/employees_per_department>

Domain: SQL · Difficulty: easy · Seniority: L5

## Problem

Surface the employee count per department, ranked from largest to smallest. For ties, use alphabetical department name so the output is deterministic.

## Worked solution and explanation

### Why this problem exists in real interviews

This is a foundational aggregation question that verifies basic SQL fluency. The interviewer uses it as a warm-up before harder problems.

---

### Break down the requirements

#### Step 1: Group by department

`GROUP BY department` creates one output row per department.

#### Step 2: Count and order

`COUNT(*)` gives the headcount. Sort descending for the largest first, with alphabetical tie-breaking on `department`.

---

### The solution

**Basic GROUP BY with COUNT**

```sql
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC, department ASC
```

> **Cost Analysis**
>
> Single hash aggregate. With an index on `department`, the engine can use an index-only scan.

> **Interviewers Watch For**
>
> This is a warm-up. Getting this wrong is a strong negative signal.

> **Common Pitfall**
>
> Selecting a non-aggregated column not in GROUP BY is a syntax error in strict SQL mode.

---

## Common follow-up questions

- How would you include departments with zero employees? _(Tests LEFT JOIN from a departments reference table.)_
- What if you wanted only the top 3 largest? _(Tests LIMIT usage.)_
- How does COUNT(*) differ from COUNT(employee_id)? _(Tests NULL handling in aggregates.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/employees_per_department)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.