Find the Highest-Paid Employee in Each Department
Company: Otter.Ai
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
The interview report explicitly identified the “Department Highest Salary” SQL exercise. Use the following self-contained PostgreSQL schema:
```text
departments
-----------
department_id BIGINT PRIMARY KEY
name TEXT NOT NULL
employees
---------
employee_id BIGINT PRIMARY KEY
name TEXT NOT NULL
salary NUMERIC NOT NULL
department_id BIGINT NOT NULL REFERENCES departments(department_id)
```
Write one PostgreSQL query that returns every employee whose salary equals the highest salary in that employee's department.
Return exactly these columns:
- `department`: the department name
- `employee`: the employee name
- `salary`: the employee salary
If several employees tie for the highest salary in a department, return all of them. Departments with no employees do not appear. Do not assume department names or employee names are unique. The relative order of returned rows does not matter.
Overview: Work through a tie-aware PostgreSQL problem that finds every highest-paid employee in each department. It assesses relational reasoning, correct joins, duplicate-name handling, and precise output semantics without assuming a single winner.
Using PostgreSQL, return every employee whose salary equals the highest salary in that employee's department. Return exactly three columns: department (the department name), employee (the employee name), and salary. If multiple employees tie for the highest salary in a department, return all of them. Departments with no employees must not appear. Do not assume department names or employee names are unique. The relative order of rows does not matter.
Tables
departments(department_id BIGINT, name TEXT)
employees(employee_id BIGINT, name TEXT, salary NUMERIC(12,2), department_id BIGINT)
Hints
- Rank employees within each department rather than across the whole company.
- DENSE_RANK with salary descending keeps every employee tied at the maximum.