Most Asked SQL Problem in Data Analyst Interview Question | Data Analytics SQL Server Task

In this video we will see the most asked sql problem in data analyst interview question. We have a employee details with department and we need to get the list of employees who are getting max (highest) salary in each department. I have solved this SQL problem using two methods.
In this video I have explain cte (common table expression), joins in sql server & how to use Dense_Rank window function in sql server.
=========================================
This video solves below queries.
=========================================
1. Most asked sql problem for data analyst interview.
2. SQL task for data analyst profile.
3. SQL window functions.
4. Joins in SQL Server.
5. CTE (common table expression) in sql server.
6. How to find list of employees with highest salary in each department.
=========================================
Take a look, You may find below vides interesting.
1. SQL Problem that I failed initially to solve as a data analyst.
2. Get food delivery app loyal customers.
3. Get missing month & sales amount using datetime functions.
=========================================
SQL Queries - Table DDL, Insert Statement & solution query.
=========================================
CREATE TABLE DBO.TBL_EMP_SAL
(
EID INT NOT NULL,
ENAME VARCHAR(10) NOT NULL,
EDEPT VARCHAR(10) NOT NULL,
ESAL INT NOT NULL
);
GO
INSERT INTO DBO.TBL_EMP_SAL(EID,ENAME,EDEPT,ESAL)VALUES
(121,'TOM','HR', 1000),
(123,'SAM','HR', 2000),
(124,'RAHUL','HR', 3000),
(125,'JAN','HR', 4000),
(126,'AMOL','FA', 1000),
(127,'ROHIT','FA', 2000),
(128,'DINESH','FA', 5000),
(129,'SURESH','FA', 6000),
(111,'RAMESH','BE', 3000),
(112,'SUNIL','BE', 2000),
(113,'RAJEEV','BE', 1000),
(114,'SAGAR','BE', 1000),
(115,'RAJ','BE', 500),
(116,'MANDAR','ADMIN', 4000),
(117,'SACHIN','ADMIN', 9000),
(118,'SISHIL','ADMIN', 10000),
(119,'SAMEER','ADMIN', 6000),
(101,'MAHESH','TA', 5000),
(102,'ABHIJIT','TA', 4000),
(103,'AMAR','TA', 3000),
(103,'JITESH','TA', 2000);
GO
SELECT * FROM DBO.TBL_EMP_SAL;
GO
--=======================================
--Find employees with highest salary in each department
--=======================================
;WITH CTE_C AS
(
SELECT
ES.EDEPT,
MAX(ES.ESAL) AS MAX_SAL
FROM DBO.TBL_EMP_SAL ES
GROUP BY ES.EDEPT
)
SELECT
ES2.*
FROM DBO.TBL_EMP_SAL ES2
INNER JOIN CTE_C C
ON ES2.EDEPT=C.EDEPT AND ES2.ESAL=C.MAX_SAL;
--=======================================
--Find employees with highest salary in each department - Using DENSE_RANK()
--=======================================
;WITH CTE_C AS
(
SELECT
ES.*,
DENSE_RANK()OVER(PARTITION BY ES.EDEPT ORDER BY ES.ESAL DESC) AS SAL_RNK
FROM DBO.TBL_EMP_SAL ES
)
SELECT *
FROM CTE_C C
WHERE C.SAL_RNK=1
=========================================
#bitechlake #bizzintelligence

[bankimdas]

I did the above question in this way, Please have a look on it sir,

WITH cte AS(
select EID, ENAME, EDEPT, ESAL,
MAX(esal) OVER(PARTITION BY edept) as max_sal
from emp_sal
)
select EID, ENAME, EDEPT, ESAL
from cte
WHERE esal = max_sal
ORDER BY ESAL;