Katkat
Back

Find the Highest-Paid Employee in Each Department

Otter.AiData Engineer · Technical Screen
MediumData EngineerTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.

The interview report explicitly identified the “Department Highest Salary” SQL exercise. Use the following self-contained PostgreSQL schema:

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.