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)?