Katkat
Back

Find top-paid employee per department

TikTokData Scientist · Technical Screen
EasyData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.

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-up questions

  • 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)?