Find top-paid employee per department

Given the following tables:

1. employees

• employee_id INT PRIMARY KEY
• employee_name VARCHAR

2. employee_department

• employee_id INT
• department_id INT
• salary NUMERIC(12,2)
• updated_at TIMESTAMP

Assumptions:

• An employee can appear in multiple departments (multiple rows with different department_id).
• There may also be multiple records for the same (employee_id, department_id) over time; the latest record is the one with the greatest updated_at.

Task A:

• Write a SQL query to return the top 1 highest-paid employee in each department.
• Output columns: department_id, employee_id, employee_name, salary.

Task B (follow-ups):

• If an employee is in multiple departments, does that change the correctness of “top 1 per department”? Why or why not?
• What data issues could actually change the result (e.g., duplicates, salary history)?
• If the same employee ends up being top-paid in multiple departments but you want each employee to appear at most once in the final output, how would you modify the query? (Define a deterministic tie-break rule.)

Find top-paid employee per department

Return the top 1 highest-paid employee in each department using the most recent row per (employee_id, department_id). Output: department_id, employee_id, employee_name, salary. Then discuss whether multi-department employees affect correctness, what data issues could change results, and how to modify the query so each employee appears at most once with a deterministic tie-break.

Table employees

column	type	constraints
employee_id	INT	PRIMARY KEY
employee_name	VARCHAR

Sample data:

employee_id	employee_name
1	Alice
2	Bob
3	Carol
4	Dave
5	Erin

Table employee_department

column	type	constraints
employee_id	INT
department_id	INT
salary	NUMERIC(12,2)
updated_at	TIMESTAMP

Sample data:

department_id	employee_id	salary	updated_at
10	1	100000	2024-01-01 10:00:00
10	1	110000	2024-06-01 09:00:00
10	2	115000	2024-05-01 12:00:00
10	3	115000	2024-05-02 08:30:00
20	2	80000	2023-12-01 12:00:00
20	2	99000	2024-02-01 09:15:00
20	4	120000	2024-04-01 11:00:00
20	5	120000	2024-04-02 10:00:00
30	1	130000	2024-03-01 09:00:00
30	4	125000	2024-03-02 09:00:00
40	1	105000	2024-04-15 13:45:00
40	5	95000	2024-03-01 14:00:00

Expected output

One row per department with the employee who has the highest current salary in that department. Current means the row with the greatest updated_at per (employee_id, department_id). If multiple employees tie on salary within a department, break ties deterministically by smaller employee_id.

department_id	employee_id	employee_name	salary
10	2	Bob	115000
20	4	Dave	120000
30	1	Alice	130000
40	1	Alice	105000

Hints

1. Use a window function to isolate the latest row per (employee_id, department_id): ROW_NUMBER() OVER (PARTITION BY employee_id, department_id ORDER BY updated_at DESC, salary DESC). Keep rn = 1.
2. Then rank employees within each department by salary DESC and break ties deterministically (e.g., employee_id ASC). Keep dept_rn = 1.
3. Join to employees to fetch employee_name for the winners.
4. An employee being in multiple departments does not affect correctness for 'top 1 per department' because ranking is partitioned by department.
5. Data issues that can change results: duplicate or conflicting rows for the same (employee_id, department_id, updated_at), identical timestamps across updates, NULL updated_at, out-of-order timestamps, and salary ties without a defined tie-breaker.
6. To ensure each employee appears at most once globally: after the 'latest' step, pick a single department per employee with ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY salary DESC, department_id ASC) = 1 and filter to those; note this may leave some departments without a row. To still keep one row per department while enforcing uniqueness, you need an iterative assignment (e.g., a recursive CTE) that replaces suppressed top employees with the next-ranked candidate per department.
7. Performance: consider indexes on employee_department(employee_id, department_id, updated_at DESC) and on employee_department(department_id, salary DESC).