Find top-paid employee per department
Company: ByteDance
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Given the following tables:
1) employees
- employee_id INT PRIMARY KEY
- employee_name VARCHAR
2) employee_department
- employee_id INT
- department_id INT
- salary NUMERIC(12,2)
- updated_at TIMESTAMP
Assumptions:
- An employee can appear in multiple departments (multiple rows with different department_id).
- There may also be multiple records for the same (employee_id, department_id) over time; the latest record is the one with the greatest updated_at.
Task A:
- Write a SQL query to return the top 1 highest-paid employee in each department.
- Output columns: department_id, employee_id, employee_name, salary.
Task B (follow-ups):
- If an employee is in multiple departments, does that change the correctness of “top 1 per department”? Why or why not?
- What data issues could actually change the result (e.g., duplicates, salary history)?
- If the same employee ends up being top-paid in multiple departments but you want each employee to appear at most once in the final output, how would you modify the query? (Define a deterministic tie-break rule.)
Overview: This question evaluates proficiency in data manipulation and SQL querying, testing concepts such as selecting the latest records, group-wise aggregation for top-N per group, handling duplicates and salary history, and deterministic tie-breaking when an employee appears in multiple departments.
Return the top 1 highest-paid employee in each department using the most recent row per (employee_id, department_id). Output: department_id, employee_id, employee_name, salary. Then discuss whether multi-department employees affect correctness, what data issues could change results, and how to modify the query so each employee appears at most once with a deterministic tie-break.
Tables
employees(employee_id INT, employee_name VARCHAR)
employee_department(employee_id INT, department_id INT, salary NUMERIC(12,2), updated_at TIMESTAMP)
Hints
- Use a window function to isolate the latest row per (employee_id, department_id): ROW_NUMBER() OVER (PARTITION BY employee_id, department_id ORDER BY updated_at DESC, salary DESC). Keep rn = 1.
- Then rank employees within each department by salary DESC and break ties deterministically (e.g., employee_id ASC). Keep dept_rn = 1.