Finds the top two highest-paid employees in each department

Write a SQL query to:

1. Find the top 2 highest-paid employees in each department.

2. Show their ranking based on salary within the department.

3. Also calculate the department average salary.

4. Only include employees who joined before 2022.

Step 1: Create Table

CREATE TABLE Employee (
EmpId INT PRIMARY KEY,
Name VARCHAR(50),
Department VARCHAR(50),
Salary INT,
JoiningDate DATE,
ManagerId INT
);

Step 2: Insert Sample Data

INSERT INTO Employee (
EmpId,
Name,
Department,
Salary,
JoiningDate,
ManagerId
)
VALUES
(1, 'Aarav', 'IT', 90000, '2019-01-10', NULL),
(2, 'Priya', 'IT', 75000, '2020-03-15', 1),
(3, 'Rohit', 'IT', 72000, '2021-02-20', 1),
(4, 'Meera', 'HR', 60000, '2020-08-01', NULL),
(5, 'Aditya', 'HR', 55000, '2021-05-10', 4),
(6, 'Kavya', 'Finance', 80000, '2018-07-23', NULL),
(7, 'Rahul', 'Finance', 78000, '2020-10-12', 6),
(8, 'Ananya', 'Finance', 79000, '2019-09-05', 6),
(9, 'Dev', 'IT', 95000, '2022-01-01', 1),
(10, 'Sneha', 'HR', 61000, '2019-11-11', 4);

Table: Employee

Solution:

This query finds the top two highest-paid employees in each department (among those who joined before 2022) and also shows the average salary of the department for context.

WITH RankedEmp AS (
SELECT
EmpId,
Name,
Department,
Salary,
JoiningDate,
DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS SalaryRank,
AVG(Salary) OVER (PARTITION BY Department) AS DeptAvgSalary
FROM Employee
WHERE JoiningDate < '2022-01-01'
)
SELECT Department, Name, Salary, SalaryRank, DeptAvgSalary
FROM RankedEmp
WHERE SalaryRank <= 2
ORDER BY Department, SalaryRank;

Read Also..

SQL Interview Questions