Find the Highest-Paid Employee in Each Department

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.

Find the Highest-Paid Employees in Each Department

Table departments

column	type	constraints
department_id	BIGINT	PRIMARY KEY
name	TEXT	NOT NULL

Sample data:

department_id	name
10	Engineering
20	Sales
30	Research
40	Sales
50	Operations

Table employees

column	type	constraints
employee_id	BIGINT	PRIMARY KEY
name	TEXT	NOT NULL
salary	NUMERIC(12,2)	NOT NULL
department_id	BIGINT	NOT NULL, REFERENCES departments(department_id)

Sample data:

department_id	employee_id	name	salary
10	1	Ada	120000
10	2	Lin	150000
10	3	Noor	150000
20	4	Kai	90000
20	5	Sam	110000
20	6	Sam	105000
30	7	Priya	130000
40	8	Sam	110000
40	9	Mia	110000

Expected output

department	employee	salary
Engineering	Lin	150000
Engineering	Noor	150000
Sales	Sam	110000
Research	Priya	130000
Sales	Sam	110000
Sales	Mia	110000

Hints

1. Rank employees within each department rather than across the whole company.
2. DENSE_RANK with salary descending keeps every employee tied at the maximum.
3. Join departments only after the per-department salary rank has been computed.