Deloitte SQL Interview Question 2024 | Find the top 3 highest-paid employees in each department

In this video, I have discussed how to solve the SQL Interview Questions:
Part 1: Find the top 3 highest-paid employees in
each department.
Part 2: Find the average salary of employees hired
in the last 5 years.
Part 3: Find the employees whose salry is less than
the average salary of employees hired in
the last 5 years.
-- Query to create table:
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DepartmentID INT,
Salary DECIMAL(10, 2),
DateHired DATE,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
-- Insert data
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'HR'),
(2, 'Engineering'),
(3, 'Sales');
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID, Salary, DateHired) VALUES
(1, 'Alice', 'Smith', 1, 50000, '2020-01-15'),
(2, 'Bob', 'Johnson', 1, 60000, '2018-03-22'),
(3, 'Charlie', 'Williams', 2, 70000, '2019-07-30'),
(4, 'David', 'Brown', 2, 80000, '2017-11-11'),
(5, 'Eve', 'Davis', 3, 90000, '2021-02-25'),
(6, 'Frank', 'Miller', 3, 55000, '2020-09-10'),
(7, 'Grace', 'Wilson', 2, 75000, '2016-04-05'),
(8, 'Henry', 'Moore', 1, 65000, '2022-06-17');
#sql #postgresql #deloitte #deloittejobs #mysql #interview #dataengineering #dataanalytics #dataanalysis #capgemini #deloitte #deloittejobs #capgeminihiring

[@varmakvm4812]

Hi,
In first question while finding the top 3 employees within each department, dense_rank() would be appropriate one because it will handle tie values without skipping the ranking rather than row_number IMO.
Thank you for the constant motivation through SQL Questions ! Looking for more ...

[@skilltechath0n]

Yes, definitely if the interviewer asked us to provide the same rank for duplicate values.
Thanks for mentioning it 👍

[@parthchauhan9305]

On the contrary, using dense_rank may give us more than 3 values (if there are ties), rather we only need to find top 3 highest paid employees (3 rows), row_number will always ensure that we get only 3 rows in the result set.