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.
Quick Answer: 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.