Find top-paid employee per department
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
## 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)?
Quick Answer: This question evaluates a candidate's competency in SQL data manipulation, including aggregation, ranking, handling of duplicate and historical rows, tie semantics, and temporal data interpretation when determining the top-paid employee per department.