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.
Community answers
Answer by michelleyuu
We want to use DENSE_RANK(), since it allows us to rank with ties, the partition will be used to filter based on the grouping which allows us to have grouped ranking.
WITH CTE AS
(SELECT
d.department_id,
department_name,
e.employee_id,
employee_name,
salary,
DENSE_RANK() OVER (PARTITION BY department_name ORDER BY salary DESC) AS ranking
FROM departments d
JOIN employee_department_comp c
ON d.department_id = c.department_id
JOIN employees e
ON e.employee_id = c.employee_id)
SELECT
department_id,
department_name,
employee_id,
employee_name,
salary
FROM CTE
WHERE ranking = 1