Katkat
Back

Find top-paid employee per department

ByteDanceData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

This problem tests your ability to perform group-wise filtering and ranking using window functions or joins to extract top-performing records within categories.

Problem Statement

You are given two tables: employees and salaries.

employees table:

  • emp_id (int): Unique identifier for the employee.
  • dept_id (int): Identifier for the department.
  • name (string): Employee name.

salaries table:

  • emp_id (int): Identifier for the employee.
  • salary (int): The employee's current salary.
  • effective_date (date): When this salary became effective.

Your task is to write a query to find the employee with the highest salary for each department.

Requirements:

  1. If an employee has multiple entries in the salaries table, use only the record with the most recent effective_date.
  2. If multiple employees in the same department share the same highest salary, return all of them.
  3. Return the dept_id, name, and salary.

Examples:

Input: employees: [(1, 10, 'Alice'), (2, 10, 'Bob'), (3, 20, 'Charlie')] salaries: [(1, 80000, '2023-01-01'), (2, 90000, '2023-01-01'), (3, 70000, '2023-01-01')]

Output:

dept_idnamesalary
10Bob90000
20Charlie70000

Constraints:

  • Use standard SQL syntax (e.g., PostgreSQL or MySQL).
  • Consider cases where an employee might have changed departments, but only the current department ID in the employees table matters.

Hints

Hint 1

First, create a subquery or CTE to identify the most recent salary for each employee by partitioning by emp_id.

Hint 2

Use a window function like RANK() or DENSE_RANK() to assign a ranking to employees within their respective departments based on their current salary.

Hint 3

Filter the final result to keep only the rows where the rank is equal to 1.

Follow-up questions

  • How would you modify your query if the requirement changed to return the top 3 highest-paid employees per department?
  • What impact would an index on `emp_id` and `effective_date` have on the performance of this query?
  • How would you handle departments that currently have zero employees registered in the database?