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

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 Interview QnAs: • SQL Interview Question...
PySpark Basic to Advance: • PySpark Tutorial
Python Basic to Advance Hands-on: • Python for Data Scienc...
Resume Building and Interview Preps: • Resume Building and In...
***Join our Telegram Group for regular updates on Jobs and Data Science materials:
t.me/datascien... 🔥
t.me/datascien... 🔥
Instagram:
bit.ly/3LKnSmw 🔥
bit.ly/3LKnSmw 🔥
LinkedIn:
/ data. . 🔥
/ data. . 🔥
#sql #postgresql #deloitte #deloittejobs #capgeminihiring #cognizant #infosys #tcs #genpactjobs #mysql #interview #dataengineering #dataanalytics #dataanalysis #capgemini #capgeminijobs #sqlfordataengineer #jobinterview #coding #programming #technicalinterview

[@chinthalakishore7054]

Great explanation sir❤

[@datasciencecorner]

Thanks and welcome! Keep watching :)

[@Hope-xb5jv]

use Dense_rank instead of row_number because your logic fail when two employees have same salary in same department
----Assuming you want different salary top 3 salary employees
But if you want only top 3 employees regardless of their same salary or different salary then row number is correct

[@datasciencecorner]

Yes, Dense_rank is more beneficial when you two or more employees with similar salary and you only want the 3 distinct top salary.