Find top-paid employee per department

Tables

Assume the company stores employee compensation by department assignment.

employee_dept_salary

• employee_id INT
• employee_name VARCHAR
• department_id INT
• department_name VARCHAR
• salary NUMERIC
• effective_date DATE (optional; if present, assume you want the most recent salary)

Notes:

• An employee may appear in multiple departments (e.g., matrix org or multiple assignments).
• There may be multiple rows per (employee_id, department_id) (e.g., historical changes) unless otherwise stated.

Task

Write a SQL query to return the top 1 highest-paid employee per department.

Output

For each department, return:

• department_id, department_name
• employee_id, employee_name
• salary

Follow-ups

1. If an employee can have records in multiple departments, does that affect the result? Explain.
2. What would affect the result (e.g., ties, duplicates, history rows)?
3. If the query can return multiple employees due to ties, but you only want one row per department, how would you enforce that (define a deterministic tie-break)?

Top-paid employee(s) per department (allow ties)

You are given employee-to-department compensation records. An employee may appear in multiple departments (one row per employee per department).

Write a SQL query to return the employee(s) with the highest salary in each department. If multiple employees tie for the top salary within a department, return all of them.

Return columns: department_id, department_name, employee_id, employee_name, salary.

Table departments

column	type	constraints
department_id	INT	PRIMARY KEY, NOT NULL
department_name	VARCHAR(50)	NOT NULL

Sample data:

department_id	department_name
10	Engineering
20	Sales
30	HR

Table employees

column	type	constraints
employee_id	INT	PRIMARY KEY, NOT NULL
employee_name	VARCHAR(50)	NOT NULL

Sample data:

employee_id	employee_name
1	Alice
2	Bob
3	Cathy
4	Dan
5	Eve

Table employee_department_comp

column	type	constraints
record_id	INT	PRIMARY KEY, NOT NULL
employee_id	INT	NOT NULL
department_id	INT	NOT NULL
salary	DECIMAL(12,2)	NOT NULL

Sample data:

department_id	employee_id	record_id	salary
10	1	1	120000
10	2	2	120000
10	3	3	90000
20	1	4	110000
20	4	5	125000
20	3	6	130000
30	5	7	95000
30	2	8	97000
30	4	9	97000

Expected output

One row per (department, top-paid employee). Ties are included, so a department may have multiple rows if multiple employees share the top salary.

department_id	department_name	employee_id	employee_name	salary
10	Engineering	1	Alice	120000
10	Engineering	2	Bob	120000
20	Sales	3	Cathy	130000
30	HR	2	Bob	97000
30	HR	4	Dan	97000

Hints

1. Use a window function like DENSE_RANK() partitioned by department.
2. Filter to rank = 1 to keep the top salary per department; DENSE_RANK keeps ties.

Top-paid employee per department (exactly one row per department)

Using the same tables, return exactly one top-paid employee per department.

If multiple employees tie for the highest salary within a department, break ties by choosing the smallest employee_id.

Return columns: department_id, department_name, employee_id, employee_name, salary.

Table departments

column	type	constraints
department_id	INT	PRIMARY KEY, NOT NULL
department_name	VARCHAR(50)	NOT NULL

Sample data:

department_id	department_name
10	Engineering
20	Sales
30	HR

Table employees

column	type	constraints
employee_id	INT	PRIMARY KEY, NOT NULL
employee_name	VARCHAR(50)	NOT NULL

Sample data:

employee_id	employee_name
1	Alice
2	Bob
3	Cathy
4	Dan
5	Eve

Table employee_department_comp

column	type	constraints
record_id	INT	PRIMARY KEY, NOT NULL
employee_id	INT	NOT NULL
department_id	INT	NOT NULL
salary	DECIMAL(12,2)	NOT NULL

Sample data:

department_id	employee_id	record_id	salary
10	1	1	120000
10	2	2	120000
10	3	3	90000
20	1	4	110000
20	4	5	125000
20	3	6	130000
30	5	7	95000
30	2	8	97000
30	4	9	97000

Expected output

Exactly one row per department. When multiple employees tie for top salary, the smallest employee_id is chosen.

department_id	department_name	employee_id	employee_name	salary
10	Engineering	1	Alice	120000
20	Sales	3	Cathy	130000
30	HR	2	Bob	97000

Hints

1. ROW_NUMBER() (not DENSE_RANK) will force one row per department.
2. Add a deterministic tie-breaker (e.g., employee_id) to the ORDER BY inside the window function.