Find top-paid employee per department

Q: Find top-paid employee per department

This is a Data Manipulation (SQL/Python) interview question from ByteDance for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

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

If an employee can have records in multiple departments, does that affect the result? Explain.
What would affect the result (e.g., ties, duplicates, history rows)?
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)?