Uber SQL interview questions – Leetcode 185 – Department Top Three Salaries

Problem –

A company’s executives are interested in seeing who earns the most money in each of the company’s departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.

Write an SQL query to find the employees who are high earners in each of the departments.

Return the result table in any order.

The query result format is in the following example:

Difficulty Level – Hard

Solution –

SELECT DISTINCT
    Department,
    EMployee,
    Salary
FROM (
SELECT 
    d.Name as Department,
    e.Name as Employee,
    e.Salary as Salary,
    DENSE_RANK() OVER (PARTITION BY d.Name ORDER BY e.Salary DESC) as rnk
FROM Employee as e
JOIN Department as d
ON e.DepartmentId = d.Id
    ) t1
WHERE rnk <= 3

To solve this problem, we are using the DENSE_RANK() function and Partitioning the data by Department, so that we have a separate ranking of salary for each individual department as we are asked to do so. And we are ordering the salary in Desc order so that the highest salary gets the ranking of 1 and so on. And later in the outer query, we are filtering the data where the salary rank is less than or equal to 3 to get the top three salaries for that department. We also used DISTINCT to select only one salary in case of a tie.

This is the third version of common SQL interview questions related to salary. For others read these –

Version 1 – https://lifewithdata.com/2021/08/01/amazon-sql-interview-questions-leetcode-177-nth-highest-salary/

Version 2 – https://lifewithdata.com/2021/08/02/sql-interview-questions-leetcode-176-second-highest-salary/

Rating: 1 out of 5.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s