SQL: Highest-Paid Employee in Each Department, Sorted by Department Name
Company: Meta
Role: Product Manager
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Other
You are given a company database with the four tables below. Write a SQL query that returns the top earner in each department, with the result sorted by department name.
**employees**
| Column | Type | Meaning |
|---|---|---|
| id | int | Employee identifier (primary key) |
| first_name | varchar | Employee's first name |
| last_name | varchar | Employee's last name |
| salary | int | Employee's salary |
| department_id | int | The employee's department; references `departments.id` |
**departments**
| Column | Type | Meaning |
|---|---|---|
| id | int | Department identifier (primary key) |
| name | varchar | Department name |
**projects**
| Column | Type | Meaning |
|---|---|---|
| id | int | Project identifier (primary key) |
| title | varchar | Project title |
| start_date | date | Project start date |
| end_date | date | Project end date |
| budget | int | Project budget |
**employees_projects** (links employees to the projects they work on)
| Column | Type | Meaning |
|---|---|---|
| project_id | int | References `projects.id` |
| employee_id | int | References `employees.id` |
Return exactly these columns, in this order:
| Column | Type |
|---|---|
| department_name | varchar |
| employee_id | int |
| first_name | varchar |
| last_name | varchar |
| salary | int |
```hint Compare within a department
Each employee should only be measured against colleagues in the same department. Before writing the query, decide what the result should contain when two people share a department's highest salary.
```
### Clarifying Questions
- If two or more employees tie for the highest salary in a department, should all of them be returned, or exactly one (and then which one)?
- Should a department with no employees appear in the result, for example with empty employee columns?
- Can `salary` or `department_id` be NULL, and if so, how should those employees be treated?
- Within one department, how should tied rows be ordered so the output is deterministic?
### What a Strong Answer Covers
- A correct join from employees to departments that returns exactly the requested columns in the requested order
- A per-department maximum rather than a company-wide one, computed with a window function or a grouped subquery
- An explicit, defended rule for ties, plus a tie-break that makes the row order deterministic
- Correct treatment of NULL salaries, employees without a department and departments with no employees
- Recognizing that the project tables play no part in this query
### Follow-up Questions
- Return the three highest earners per department instead. How do `RANK`, `DENSE_RANK` and `ROW_NUMBER` give different answers when salaries tie?
- Add each department's average salary and the gap between its top earner and that average.
- Rank only employees assigned to at least one project. How do you avoid counting an employee once per project?
Overview: A SQL exercise over an employees, departments and projects schema that asks for the highest-paid employee in every department, sorted by department name. It tests per-group ranking, a clear rule for salary ties, NULL handling, and recognizing which tables the query actually needs.