Finding Top Earners: A T-SQL Challenge in SQL Server

I have recently taken a consulting engagement Comprehensive Database Performance Health Check, where I have to improve the performance of many SQL Server Queries by re-writing them. In this blog post, we’ll explore an intriguing SQL challenge that involves two tables – Employees and Departments. The goal is to find the top three highest-paid (top earners) employees in each department and their corresponding department details. We’ll walk through two different T-SQL solutions to tackle this problem, each offering unique insights into solving complex queries.

Setting the Stage

Let’s start by understanding the structure of the Employees and Departments tables and their respective data. The Employees table contains EmployeeID, FirstName, LastName, DepartmentID, and Salary columns. On the other hand, the Departments table consists of DepartmentID, DepartmentName, and Location columns.

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  DepartmentID INT,
  Salary DECIMAL(10, 2)
);
CREATE TABLE Departments (
  DepartmentID INT PRIMARY KEY,
  DepartmentName VARCHAR(50),
  Location VARCHAR(50)
);
INSERT INTO Departments VALUES
(1, 'HR', 'New York'),
(2, 'Engineering', 'San Francisco'),
(3, 'Finance', 'Chicago');
INSERT INTO Employees VALUES
(1, 'John', 'Doe', 1, 70000),
(2, 'Jane', 'Smith', 1, 80000),
(3, 'Tom', 'Brown', 1, 75000),
(4, 'Emma', 'Johnson', 1, 80000),
(5, 'Sam', 'Davis', 2, 90000),
(6, 'Olivia', 'Garcia', 2, 85000),
(7, 'Liam', 'Harris', 2, 95000),
(8, 'Sophia', 'Clark', 2, 90000),
(9, 'Mason', 'Rodriguez', 3, 100000),
(10, 'Ava', 'Lewis', 3, 110000),
(11, 'Lucas', 'Lee', 3, 105000),
(12, 'Mia', 'Walker', 3, 100000);

Challenge Overview – Top Earners

The challenge is to write a T-SQL query that retrieves the top three highest-paid employees in each department, considering the possibility of multiple employees having the same salary. We want to ensure that if there are more than three employees with the same highest salary in a department, all of them should be included in the result.

Solution 1: Using Common Table Expressions (CTE)

We begin with a Common Table Expression (CTE) named EmployeeRank, which computes the rank of each employee within their respective department based on their salary in descending order. The PARTITION BY clause helps us group employees by department, and the ORDER BY clause arranges them by salary in descending order. We then select the top three highest-paid employees from each department using the RANK() function.

;WITH EmployeeRank AS (
  SELECT
    e.EmployeeID,
    e.FirstName,
    e.LastName,
    e.DepartmentID,
    e.Salary,
    d.DepartmentName,
    d.Location,
    DENSE_RANK() OVER (PARTITION BY e.DepartmentID ORDER BY e.Salary DESC) AS Rank
  FROM Employees e
  JOIN Departments d ON e.DepartmentID = d.DepartmentID
)
SELECT
  EmployeeID,
  FirstName,
  LastName,
  DepartmentID,
  Salary,
  DepartmentName,
  Location
FROM EmployeeRank
WHERE Rank <= 4
ORDER BY DepartmentID, Salary DESC;

Solution 2: Utilizing Subqueries

In this approach, we employ subqueries to achieve the desired outcome. The main query involves a self-join between the Employees and Departments tables, where we retrieve employee details along with their department information. The subquery inside the WHERE clause counts the number of distinct employees in the same department with higher salaries than the current employee. We select employees whose count is less than three, ensuring that we include all employees with the same highest salary in a department.

SELECT
  e.EmployeeID,
  e.FirstName,
  e.LastName,
  e.DepartmentID,
  e.Salary,
  d.DepartmentName,
  d.Location
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE (
  SELECT COUNT(DISTINCT e2.Salary)
  FROM Employees e2
  WHERE e2.DepartmentID = e.DepartmentID AND e2.Salary > e.Salary
) < 2
ORDER BY e.DepartmentID, e.Salary DESC;

Conclusion

Solving the challenge of finding the top earners in each department using T-SQL requires a thoughtful approach and the application of advanced SQL techniques. Through two distinct solutions, we demonstrated how Common Table Expressions (CTEs) and subqueries can be utilized to accomplish this task efficiently. As SQL professionals, understanding and mastering such complex queries is crucial in optimizing database operations and generating valuable insights from your data.

Note: Feel free to try out the provided solutions with different scenarios and datasets to further enhance your SQL skills and explore the power of T-SQL in handling intricate challenges.

You can always reach out to me via YouTube Channel.

Reference: Pinal Dave (http://blog.SQLAuthority.com)