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.)
Quick Answer: 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.