Katkat
Back

Query departments and top earners

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

Last updated

This problem tests your ability to aggregate data using window functions to identify top-performing rows within specific groups and perform conditional filtering across joined tables.

Problem Statement

You are working with two tables: employees and departments.

Table Schema

employees

  • id: INT (Primary Key)
  • name: VARCHAR
  • salary: INT
  • department_id: INT

departments

  • id: INT (Primary Key)
  • name: VARCHAR

Task

Write a SQL query to identify the top earners in each department. A "top earner" is defined as an employee whose salary is strictly in the top 3 within their department. If multiple employees share the same salary, they should still be included as long as their rank is <= 3.

Expected Output

Return a result set containing the Department Name, Employee Name, and Salary, ordered by Department Name (alphabetical) and Salary (descending).

Example

Input: employees: (1, 'Alice', 90000, 1), (2, 'Bob', 80000, 1), (3, 'Charlie', 90000, 1), (4, 'David', 70000, 1), (5, 'Eve', 60000, 2) departments: (1, 'Engineering'), (2, 'Sales')

Output:

DepartmentNameSalary
EngineeringAlice90000
EngineeringCharlie90000
EngineeringBob80000
SalesEve60000

Constraints

  • Use DENSE_RANK() or RANK() appropriately to handle ties.
  • Assume department_id is a valid foreign key.

Hints

Hint 1

Join the two tables on the department ID column to associate employee records with readable department names.

Hint 2

Use a window function like DENSE_RANK() partitioned by the department to calculate the salary rank for each employee.

Hint 3

Wrap the window function in a Common Table Expression (CTE) or subquery so you can filter for ranks less than or equal to 3.

Follow-up questions

  • How would you modify the query if you only wanted to return the unique top 3 salary amounts per department, rather than the names of the employees?
  • If a department has fewer than 3 employees, how does your current logic handle that? Should it return all of them?
  • How would the query change if you needed to calculate the average salary of the top 3 earners in each department?