A SQL Practical Challenge on Salary Analysis

Recently, I encountered an interesting SQL challenge. The task required using window functions to analyze employee salaries within departments. This is a powerful yet sometimes tricky feature in SQL.

Here is the problem description and how I solved it.

Problem Description

#

sql dept salary ranking

In this MySQL challenge, your task is to analyze salary rankings within each company department. Construct a query that accomplishes the following objectives:

• Individual Salary Rankings: For each department represented by ‘DivisionID’, rank the employees based on their salaries in descending order.
• Salary Comparisons: Calculate the difference in salary (SalaryDifference) between each employee and the next lower-paid employee in the same department. For the lowest-paid employee in each department, the ‘SalaryDifference should be displayed as ‘NULL.
• Highlight Top Earners: Include a column titled ‘IsTopEarner’ that displays “Yes” for the highest-paid employee in each department and “No” for all other employees.

Notice:

• Table name: Employees
• MySQL version: 5.5.56-log
• The result should include the following columns (ordered by SalaryRank in ascending order):
  • DivisionID（ID of the department)
  • Name (Name of the employee)
  • Salary (Salary of the employee)
  • SalaryRank（(Rank of the employee’s salary within the department, with 1 being the highest salary)
  • SalaryDifference (Difference in salary between the employee and the next lower-paid employee in the same department)
  • IsTopEarner（Indicates if the employee is the top earner in their department)

-- Create the table
CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(100),
    DivisionID INT,
    ManagerID INT,
    Salary DECIMAL(10, 2)
);

-- Insert data into the Employees table
INSERT INTO Employees (ID, Name, DivisionID, ManagerID, Salary)
VALUES
(356, 'Daniel Smith', 100, 133, 40000),
(122, 'Arnold Sully', 101, NULL, 60000),
(467, 'Lisa Roberts', 100, NULL, 80000),
(112, 'Mary Dial', 105, 467, 65000),
(775, 'Dennis Front', 103, NULL, 90000),
(111, 'Larry Weis', 104, 35534, 75000),
(222, 'Mark Red', 102, 133, 86000),
(577, 'Robert Night', 105, 12353, 76000),
(133, 'Susan Wall', 105, 577, 110000);

Solution & Analysis

#

1. Analyze the Problem

#

This is a typical application of SQL window functions, which involves:

• Grouping the data by department (PARTITION BY DivisionID).
• Ranking rows based on a specific condition (RANK() or ROW_NUMBER()).
• Comparing the current row to adjacent rows (LEAD()).
• Identifying the first row in each group (FIRST_VALUE() or using conditional logic).

2. Writing the Query

#

Here is the complete query that satisfies the requirements:

SELECT
    DivisionID,
    Name,
    Salary,
    ROW_NUMBER() OVER (PARTITION BY DivisionID ORDER BY Salary DESC) AS SalaryRank,
    Salary - LEAD(Salary) OVER (PARTITION BY DivisionID ORDER BY Salary DESC) AS SalaryDifference,
    CASE
        WHEN RANK() OVER (PARTITION BY DivisionID ORDER BY Salary DESC) = 1 THEN 'Yes'
        ELSE 'No'
    END AS IsTopEarner
FROM Employees
ORDER BY DivisionID, SalaryRank;

3. Query Explanation

#

1. Salary Ranking We use RANK() or ROW_NUMBER() to rank employees by salary within each department (PARTITION BY DivisionID), ordered by salary in descending order:

RANK() OVER (PARTITION BY DivisionID ORDER BY Salary DESC) AS SalaryRank
# or
ROW_NUMBER() OVER (PARTITION BY DivisionID ORDER BY Salary DESC) AS SalaryRank

2. Salary Difference Calculation The LEAD() function retrieves the salary of the next row (lower-paid employee) within the same department (PARTITION BY DivisionID), and calculates the difference between the current and next employee’s salary:

Salary - LEAD(Salary) OVER (PARTITION BY DivisionID ORDER BY Salary DESC) AS SalaryDifference,

For the lowest-paid employee, since LEAD() cannot fetch the next row, the difference will be NULL. 3. Highlight Top Earners Using a CASE statement, we check if the employee has the highest salary in their department (SalaryRank = 1). If so, mark as “Yes”, otherwise “No”:

CASE
    WHEN RANK() OVER (PARTITION BY DivisionID ORDER BY Salary DESC) = 1 THEN 'Yes'
    ELSE 'No'
END AS IsTopEarner
# or
CASE
    WHEN ROW_NUMBER() OVER (PARTITION BY DivisionID ORDER BY Salary DESC) = 1 THEN 'Yes'
    ELSE 'No'
END AS IsTopEarner

Expected Results

#

The query results will be:

Key Takeaways

#

1. Powerful Window Functions Window functions like RANK(), ROW_NUMBER(), and LEAD() are very effective when performing calculations within groups of data.
2. Modular Query Writing Breaking down the task into smaller parts (such as ranking, calculating differences, and conditional logic) helps simplify the problem-solving process and ensures the query’s clarity and maintainability.
3. Efficient Debugging Testing individual parts of the query (such as first checking the output of RANK(), then adding the LEAD() calculation) can speed up the debugging process.

Conclusion

#

This challenge was a great practice to help me apply SQL window functions to solve a real-world problem. From ranking salaries to comparing adjacent row values, and identifying top earners, this query demonstrates how window functions can simplify complex tasks into concise solutions. If you are facing similar challenges, mastering these window functions will greatly improve your SQL skills and data analysis abilities. I’ve written an in-depth article on window functions, feel free to read and share your thoughts!