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