Quick Overview

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.

Find top-paid employee per department

Company: TikTok

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: easy

Interview Round: Technical Screen

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

Overview: 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.

Top-paid employee(s) per department (allow ties)

You are given employee-to-department compensation records. An employee may appear in multiple departments (one row per employee per department). Write a SQL query to return the employee(s) with the highest salary in each department. If multiple employees tie for the top salary within a department, return all of them. Return columns: department_id, department_name, employee_id, employee_name, salary.

Tables

departments(department_id INT, department_name VARCHAR(50))

employees(employee_id INT, employee_name VARCHAR(50))

employee_department_comp(record_id INT, employee_id INT, department_id INT, salary DECIMAL(12,2))

Hints

  1. Use a window function like DENSE_RANK() partitioned by department.
  2. Filter to rank = 1 to keep the top salary per department; DENSE_RANK keeps ties.

Top-paid employee per department (exactly one row per department)

Using the same tables, return exactly one top-paid employee per department. If multiple employees tie for the highest salary within a department, break ties by choosing the smallest employee_id. Return columns: department_id, department_name, employee_id, employee_name, salary.

Tables

departments(department_id INT, department_name VARCHAR(50))

employees(employee_id INT, employee_name VARCHAR(50))

employee_department_comp(record_id INT, employee_id INT, department_id INT, salary DECIMAL(12,2))

Hints

  1. ROW_NUMBER() (not DENSE_RANK) will force one row per department.
  2. Add a deterministic tie-breaker (e.g., employee_id) to the ORDER BY inside the window function.

Community answers

Answer by michelleyuu

We want to use DENSE_RANK(), since it allows us to rank with ties, the partition will be used to filter based on the grouping which allows us to have grouped ranking. WITH CTE AS (SELECT d.department_id, department_name, e.employee_id, employee_name, salary, DENSE_RANK() OVER (PARTITION BY department_name ORDER BY salary DESC) AS ranking FROM departments d JOIN employee_department_comp c ON d.department_id = c.department_id JOIN employees e ON e.employee_id = c.employee_id) SELECT department_id, department_name, employee_id, employee_name, salary FROM CTE WHERE ranking = 1

Loading coding console...