MS SQL Server 22 - Finding the Nth Highest Salary

In this unique article, we'll explore a scenario where we manage interview data and analyze salaries to find the nth highest salary using SQL queries. Let's dive into the details step by step.

Step 1: Setting Up the Database

Firstly, let's set up a database named MyInterviewDB and create a table Interviews to store interview details such as company name, date attended, and salary accepted.

CREATE DATABASE MyInterviewDB;
USE MyInterviewDB;

CREATE TABLE Interviews (
    IntId INT PRIMARY KEY IDENTITY(1,1),
    company_name VARCHAR(100),
    date_attended DATETIME,
    salary_accepted INT
);

INSERT INTO Interviews (company_name, date_attended, salary_accepted)
VALUES
    ('HCL', DATEADD(day, -2, GETDATE()), 3100000),
    ('Cognizant', DATEADD(day, -10, GETDATE()), 3000000),
    ('Mediasist', DATEADD(day, -4, GETDATE()), 2800000),
    ('Tesco', DATEADD(day, -5, GETDATE()), 3100000),
    ('Simens', DATEADD(day, -6, GETDATE()), 3100000),
    ('CBA', DATEADD(day, -20, GETDATE()), 3100000),
    ('First American', DATEADD(day, -9, GETDATE()), 3100000),
    ('IBM', DATEADD(day, -2, GETDATE()), 3100000),
    ('IBM', DATEADD(day, -11, GETDATE()), 3100000),
    ('HCL', DATEADD(day, -22, GETDATE()), 3100000);

Step 2: Analyzing Data

Now, let's analyze the data to find the nth highest salary using a SQL query. We'll use a Common Table Expression (CTE) with the DENSE_RANK() function to rank salaries in descending order.

-- Query to find the nth highest salary
WITH cte_nthsalary AS (
    SELECT *, DENSE_RANK() OVER (ORDER BY salary_accepted DESC) AS vrank
    FROM Interviews
)

-- Replace '1' with the desired nth value (e.g., 2nd highest, 3rd highest, etc.)
SELECT *
FROM cte_nthsalary
WHERE vrank = 1; -- Change to desired nth value

Explanation:

• Step 1 (CREATE DATABASE, CREATE TABLE, INSERT INTO): Sets up the database MyInterviewDB and creates a table Interviews to store interview data.
• Step 2 (WITH cte_nthsalary): Defines a Common Table Expression (CTE) named cte_nthsalary that calculates a dense rank (DENSE_RANK()) based on the salary_accepted column in descending order.
• Main Query (SELECT * FROM cte_nthsalary WHERE vrank = 1): Selects all columns from the CTE where the vrank (dense rank) equals 1, which corresponds to the highest salary.

Conclusion

In this article, we've covered how to set up a database to store interview data, populate it with sample data, and use SQL queries to find the nth highest salary from the interviewees' salary data. This practical example demonstrates how SQL queries can be utilized to analyze and derive insights from real-world data stored in a relational database environment. Understanding these concepts is crucial for data analysis and decision-making in various business scenarios.