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

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

A few months ago, we took on a consulting engagement where we ran a Comprehensive Database Performance Health Check and needed to improve the performance of a number of SQL Server queries by rewriting them. In this blog post, we’ll walk through an interesting SQL challenge involving two tables (Employees and Departments). The goal is to find the top three highest-paid employees (the top earners) in each department, along with their corresponding department details. To solve this problem, we’ll look at two different T-SQL solutions, each offering a unique way to approach complex queries.

Setting Up the Scenario

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

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', 'London'),

(2, 'Engineering', 'Manchester'),

(3, 'Finance', 'Plymouth');

INSERT INTO Employees VALUES

(1, 'Ethan', 'Anderson', 1, 70000),

(2, 'Olivia', 'Foster', 1, 80000),

(3, 'Benjamin', 'Clarke', 1, 75000),

(4, 'Ava', 'Mitchell', 1, 80000),

(5, 'William', 'Roberts', 2, 90000),

(6, 'Sophia', 'Edwards', 2, 85000),

(7, 'James', 'Bennett', 2, 95000),

(8, 'Isabella', 'Watson', 2, 90000),

(9, 'Alexander ', 'Nelson', 3, 100000),

(10, 'Emily', 'Turner', 3, 110000),

(11, 'Jacob', 'Parker', 3, 105000),

(12, 'Mia', 'Carter', 3, 100000);

Overview of the Scenario — Top Earners

The challenge here is to write a T-SQL query that returns the top three highest-paid employees in each department, while accounting for the possibility that multiple employees might share the same salary. If a department has more than three employees tied at the same top salary, we want all of them included in the result.

Solution 1: Using Common Table Expressions (CTEs)

We start with a Common Table Expression called EmployeeRank, which calculates each employee’s rank within their department based on salary, in descending order. The PARTITION BY clause helps us group employees by department, and the ORDER BY clause sorts them by salary in descending order. We then use the RANK() function to select the top three highest-paid employees from each department.

;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: Using Subqueries

In this approach, we use subqueries to reach the desired result. The main query performs a self-join between the Employees and Departments tables, where we retrieve employee details along with department information. The subquery in the WHERE clause counts the number of distinct employees in the same department with a higher salary than the current employee. We select employees where that count is less than three, which makes sure every employee tied at the same salary in a department is included.

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 takes a thoughtful approach and the application of advanced SQL techniques. Through two different solutions, we’ve shown how Common Table Expressions (CTEs) and subqueries can both be used to accomplish this task efficiently. As SQL practitioners, understanding and mastering these kinds of complex queries is essential for optimizing database operations and extracting valuable insights from your data.

Note: Feel free to try out the solutions provided with different scenarios and data sets to further develop your SQL skills and explore the power of T-SQL for tackling complex challenges. This kind of hands-on practice will help you advance your SQL abilities and apply them to real-world problems. Working through different scenarios and data sets involving complex queries, database design, and optimization will help you build a broader perspective. That, in turn, will help you develop the skills to solve more complex problems you may run into in real life. Remember, practicing and experimenting is an important part of advancing your SQL skills.