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)?
Overview: 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.
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.
Tables
departments(department_id INT, department_name VARCHAR(50))
employees(employee_id INT, employee_name VARCHAR(50))
employee_department_comp(record_id INT, employee_id INT, department_id INT, salary DECIMAL(12,2))
Hints
- Use a window function like DENSE_RANK() partitioned by department.
- 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.
Tables
departments(department_id INT, department_name VARCHAR(50))
employees(employee_id INT, employee_name VARCHAR(50))
employee_department_comp(record_id INT, employee_id INT, department_id INT, salary DECIMAL(12,2))
Hints
- ROW_NUMBER() (not DENSE_RANK) will force one row per department.
- Add a deterministic tie-breaker (e.g., employee_id) to the ORDER BY inside the window function.