L&T SQL Interview Problem | Print Highest and Lowest Salary Employees in Each Department

[@abhinavmahajan448]

Thanks for the video. Solved via inner join approach
select a.dep_id,a.emp_max,b.emp_min from
(select dep_id, emp_name as emp_max , dense_rank() over (partition by dep_id order by salary desc) as max_rk from employee) a
inner join
(select dep_id, emp_name as emp_min ,dense_rank() over (partition by dep_id order by salary asc) as min_rk from employee) b
on a.dep_id = b.dep_id
where a.max_rk=1 and b.min_rk = 1

[@sanyamgarg8288]

I also did via this method.

[@proteetisaikia1811]

Hi Ankit, tried in oracle:
select a.dep_id, b.emp_name as lowest_salary_emp, c.highest_salary_emp
from
(
select dep_id, min(salary) salary
from employee
group by dep_id
) a
left join employee b on a.dep_id = b.dep_id and a.salary = b.salary
left join
(
select a.*, b.emp_name as highest_salary_emp
from
(
select dep_id, max(salary) salary
from employee
group by dep_id
) a
left join employee b on a.dep_id = b.dep_id and a.salary = b.salary
) c on a.dep_id = c.dep_id
order by a.dep_id;

[@GowthamR-ro2pt]

Thanks for the video Ankit, sharing my approach.. 😁
with cte as (select emp_name,dep_id,salary,ROW_NUMBER() over (partition by dep_id order by salary desc)rn from employeez),
cte1 as (select dep_id, case when rn = 1 then emp_name end Max_sal,
case when rn = 2 then emp_name end Min_sal
from cte)
select dep_id,max(Max_sal)Maximum,max(Min_sal)Minimum from cte1
group by dep_id

[@anamikajaiswal9423]

select distinct dep_id,
max(salary) over(partition by dep_id order by dep_id) as max_salary_emp,
min(salary) over(partition by dep_id order by dep_id) as min_salary_emp
from employee

[@VikashKumar0409]

It give output as salary not names

[@bickramjitbasu5751]

with highest_lowest_sal_cte as
(select * ,
rank() over(partition by dep_id order by salary desc) as salary_rnk from employee_ankitbansal)
select dep_id,
max(case when salary_rnk=1 then emp_name end) as highest_salaried_emps_in_dep,
min(case when salary_rnk=2 then emp_name end) as lowest_salaried_emps_in_dep
from highest_lowest_sal_cte
group by dep_id;
Thanks Ankit for the video 😊

[@avi8016]

Quite an interesting way to tackle the same problem, thankyou for the detailed process

[@ankitbansal6]

Glad you enjoyed it

[@sahilummat8555]

Hello Ankit Sir
Another approach using row_number
with cte as (
select *,
ROW_NUMBER()over(partition by dep_id order by salary asc) as min_Sal,
ROW_NUMBER()over(partition by dep_id order by salary desc) as maxx_Sal
from employee)
select dep_id,
max(case when min_Sal =1 then emp_name else null end )as emp_with_min_sal
,max(case when maxx_Sal =1 then emp_name else null end) as emp_with_max_sal
from cte
group by dep_id

[@vamsikrishna7927]

AT first I tried to solve by my self by looking only question with out watching entire video.....and i pulled out correctly
thank you for your all sql videos which making me stronger day by day .
here i my approach...
WITH CTE AS
(SELECT *,
DENSE_RANK() OVER(PARTITION BY DEP_ID ORDER BY SALARY DESC) AS RANKK,
DENSE_RANK() OVER(PARTITION BY DEP_ID ORDER BY SALARY ) AS RANKK2
FROM LOL_PRAC)
SELECT
DEP_ID,
MAX(CASE WHEN RANKK=1 THEN EMP_NAME END) AS MAX_SAL,
MAX(CASE WHEN RANKK2=1 THEN EMP_NAME END) AS MIN_SAL
FROM CTE
GROUP BY DEP_ID
;

[@ankitbansal6]

Excellent

[@shekharkarade1067]

Hi brother, I used different approach and found same results.
select dep_id1, min_salry , max_salry from
(select dep_id as dep_id1, emp_name as min_salry
from employee
where salary IN (select min(salary) from employee group by dep_id)
group by dep_id) as a
left join
(select dep_id, emp_name as max_salry
from employee
where salary IN (select max(salary) from employee group by dep_id)
group by dep_id) as b
on a.dep_id1 = b.dep_id
Thank You for the video
❤

[@zizu7755]

Hi Ankit, thanks for elaboration of all problems and knowledge you share with us on this channel. It means a lot. In this case, I definitely prefer second approach.

[@IswaryaMaran]

Your videos are all very interesting and well-explained.
here's my try
using rank and count:
with emp_salary as
(select *,rank() over (partition by dep_id order by salary desc) as rnk,
count(1) over(partition by dep_id) as cont from employee)
select dep_id,
min(case when rnk=1 then emp_name else null end) as max_salary_emp,
min(case when rnk=cont then emp_name else null end) as min_salary_emp
from emp_salary
group by dep_id

[@ankitbansal6]

Looks good. Thank you 😊

[@ritudahiya6223]

Can you please explain what is significance of using min Or max before case when

[@ezequielsaldana8279]

Hi Ankit...Thanks for the content...
I can resolved it from the following way:
--------------------------------------------------------------------------
with cte as (
select dep_id, emp_name, salary,
row_number() over(partition by dep_id order by salary) as order_salary
from employees)
select distinct dep_id,
last_value(emp_name)
over(partition by dep_id order by salary range between unbounded preceding and unbounded following)
as emp_name_max_salary,
first_value(emp_name) over(partition by dep_id order by salary) as emp_name_min_salary
from cte;
--------------------------------------------------------------------------------

[@ankitbansal6]

Thanks for posting 👏

[@ajinkyamali1514]

Another approach is using Dense_rank()
select * from (
select employee.* ,dense_rank()
over (partition by dept_id order by desc/asc)
as rank1 from employee)
where rank1=1;

[@akashgoel601]

Thanks for the video, sharing my approach.. used multiple CTE instead of case.
with cte as (
select dep_id, MIN(salary) as min,MAX(salary) as max
from employee_25Dec
GROUP by dep_id), cte_max as (
SELECT b.dep_id,b.emp_name as emp_max--, b.emp_name as emp_min
from cte a join employee_25Dec b on a.dep_id = b.dep_id
where a.max = b.salary ), cte_min as (
SELECT b.dep_id,b.emp_name as emp_min--, b.emp_name as emp_min
from cte a join employee_25Dec b on a.dep_id = b.dep_id
where a.min = b.salary )
select a.dep_id, a.emp_max, b.emp_min from cte_max a join cte_min b on a.dep_id = b.dep_id

[@akshayjoshi4106]

Keeping in mind salary amount same for multiple employees, my try in Oracle SQL:
with cte as(select e.*,dense_rank() over (partition by dep_id order by salary asc) sal_low
,dense_rank() over (partition by dep_id order by salary desc) sal_high
,row_number() over (partition by dep_id order by salary asc) rn_low
,row_number() over (partition by dep_id order by salary desc) rn_high
from employee e)
select dep_id, max(case when sal_low = 1 and rn_low = 1 then emp_name end) low
,max(case when sal_high = 1 and rn_high = 1 then emp_name end) high
from cte
group by dep_id
/

[@ashishroshan5946]

Akshay Joshi
Why are you taking only one employee if multiple have the same salary. Won't it be better to use STR_AGG to have all the employee's name from a particular dep with max or min salary separated by commas in a single cell

[@girdhar3224]

my sol:
select dep_id,
case
when salary = (min(salary) over (partition by dep_id)) then emp_name end as min_sal_emp,
case
when salary = (max(salary) over (partition by dep_id)) then emp_name end as max_sal_emp
from employee
group by dep_id

[@vijiinfo]

select department_id, min(min_salary_name),max(max_salary_name) from (
select department_id, salary ,last_name ,
case when salary = (min(salary) over(partition by department_id ) ) then last_name else null end min_salary_name,
case when salary = (max(salary) over(partition by department_id ) ) then last_name else null end max_salary_name
from employees e
where e.department_id in (30,90)
group by department_id ,salary ,last_name )
group by department_id

[@sowmya6471]

Thank you for all your SQL videos. Its helping me a lot.

[@shri089]

select distinct dep_id,
first_value(salary) over(partition by dep_id order by salary desc) as max_salary_emp,
last_value(salary) over(partition by dep_id order by salary desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) as min_salary_emp
from employee

[@ankitchauhan6629]

Hello Ankit! Thank you posting such contents, Love it.
Quick question - the second solution doesn't cover the scenario where there are multiple records in the same department having same highest or same lowest salaries. When we take Min or max of emp names, it prints only one emp name based on alphabetical order.

[@santhanabharathip7808]

Check this solution
select distinct dep_id, FIRST_VALUE(emp_name) over(partition by dep_id order by salary desc) as emp_max_salary,
FIRST_VALUE(emp_name) over(partition by dep_id order by salary) as emp_min_salary
from employee

[@sowmyakrishnam1444]

please make some videos on SQL server data tools scenario based. Just love your way of teaching

[@ankitbansal6]

Thank you. Sorry I didn't get what you mean by SQL server data tools ?

[@sowmyakrishnam1444]

@@ankitbansal6 i mean SSIS,SSRS,SSAS

[@KapilKumar-hk9xk]

wow, alway mind opening yet simple solutions.

[@muhammedjasir2659]

with cte as (
select *,
ROW_NUMBER()over(partition by dep_id order by salary desc) as rn
from employee)
select dep_id,
max(case when rn =1 then emp_name else null end )as emp_with_min_sal
,max(case when rn =2 then emp_name else null end) as emp_with_max_sal
from cte
group by dep_id

[@aaravkumarsingh4018]

with cte as(
select dep_id,emp_name,
rank() over(partition by dep_Id order by salary) as low_high,
rank() over(partition by dep_Id order by salary desc) as high_low
from employee
)
select dep_id,
max(case when high_low=1 then emp_name end) as emp_name_max_salary,
max(case when low_high=1 then emp_name end) as emp_name_min_salary
from cte
group by dep_id;

[@venkatesanvenki3721]

I'm following your SQL videos last 1 month, I learned alot. Thank you so much. I plan to learn python for data analysis. Can you please recommend me any youtube channel for same kind for analysis your doing on SQL.

[@dfkgjdflkg]

This is great content. such an amount of informatiion.

[@pardhasaradhichodey5359]

select distinct dep_id,
first_value(emp_name) over (partition by dep_id order by salary desc) as emp_name_max_salary,
last_value(emp_name) over (partition by dep_id order by salary desc
rows between unbounded preceding and unbounded following) as emp_name_min_salary
from employee

[@tahakhalid324]

Hi Aknit Sir, My answer is with max_and_min_salaries as (
SELECT
emp_name,
dep_id,
salary,
dense_rank()over(partition by dep_id order by salary desc) max_salary,
dense_rank()over(partition by dep_id order by salary) min_salary
from employee
) , min_salaries_employess as (
SELECT
dep_id,emp_name as min_salary_employee
from
max_and_min_salaries
where
min_salary = 1
) , max_salaries_employess as (
SELECT
dep_id,emp_name as max_salary_employee
from
max_and_min_salaries
where
max_salary = 1 )
SELECT
max.dep_id,
max.max_salary_employee,
min.min_salary_employee
from
max_salaries_employess max
join
min_salaries_employess min
on max.dep_id=min.dep_id

[@veerendargopineni2912]

select distinct a.dep_id,a.emp_name as emp_name_max_sal ,b.emp_name as emp_name_min_sal from
(
select *, DENSE_RANK() over (partition by dep_id order by salary desc) dr from employee
) a, (select *, DENSE_RANK() over (partition by dep_id order by salary asc) dr from employee) b
where a.dr =1 and b.dr=1 and a.dep_id = b.dep_id

[@pradiptomandal6574]

with cte as
(select *,max(salary) over(partition by dep_id ) as maxi,
min(salary) over(partition by dep_id ) as mini
from salary)
select dep_id,
max(case when salary=maxi then emp_name else null end) as emp_name_max_sal,
min(case when salary=mini then emp_name else null end) as emp_name_min_sal
from cte
group by dep_id

[@mrinalbhardwaj2583]

Hey, following in my approach
with cte as(select dep_id as 'dep',emp_name as 'name',salary as 'sal',RANK() over(partition by dep_id order by salary) rk from employee1),
cte2 as(select dep as 'dep1',name as 'emp_name_max_salary' from cte where rk=(select max(rk) from cte)),
cte3 as(select dep as 'dep2',name as 'emp_name_min_salary' from cte where rk=(select min(rk) from cte))
select cte.dep,cte2.emp_name_max_salary,cte3.emp_name_min_salary from cte,cte2,cte3 where cte.dep=cte2.dep1 and cte.dep=cte3.dep2
group by cte.dep,cte2.emp_name_max_salary,cte3.emp_name_min_salary

[@chandrarahul1990]

I tried using subqueries
select dep_id, max(case when max_sal = 1 then emp_name end) as emp_name_max_salary,
min(case when min_sal = 1 then emp_name end) as emp_name_min_salary from
(select *,
dense_rank() over (partition by dep_id order by salary desc) as max_sal,
dense_rank() over (partition by dep_id order by salary asc) as min_sal
from employee)a
group by dep_id

[@mridulmaurya8383]

with abc as (
select emp_name, dep_id,salary, max(salary) over(partition by dep_id order by salary desc) as maxi,
min(salary) over(partition by dep_id order by salary asc) as mini
from employee1
)
select dep_id,
max(case when salary=maxi then emp_name end) as salary_maxi,
max(case when salary=mini then emp_name end) as salary_mini
from abc
group by dep_id

[@pr-rb6jl]

Thanks Ankit ...Superb

[@parmoddhiman678]

for mysql
with cte as (SELECT * ,
max(salary) over(partition by dep_id) as max_salary,
min(salary) over(partition by dep_id) as min_salary
FROM employee)
select c.dep_id,
max(case when salary=max_salary then emp_name end ) as max_emp,
max( case when salary=min_salary then emp_name end ) as min_emp
from cte c
group by 1

[@prajjwaljaiswal3419]

I opted 2nd approach.

[@ankitbansal6]

👍

[@CricketLivejavwad]

Thanks for sharing such a nice interview question
it takes 21 mintues to solve this query by myself.
here is my solution
with max_sal as (
select dep_id,max(salary) as max_sal from employee a
group by dep_id
),
min_sal as (
select dep_id,min(salary) as min_sal from employee a
group by dep_id
),
aaa as (
select a.dep_id,a.emp_name from employee a
inner join max_sal b on a.dep_id = b.dep_id and a.salary = b.max_sal
),
bbb as (
select a.dep_id,a.emp_name from employee a
inner join min_sal b on a.dep_id = b.dep_id and a.salary = b.min_sal
)
select aaa.dep_id,aaa.emp_name as max_salary_emp,bbb.emp_name as min_salary_emp from aaa
inner join bbb on aaa.dep_id = bbb.dep_id
order by aaa.dep_id

[@mohammedshoaib1769]

with cte as
(select *
,MAX(salary) over (partition by dep_id) as max_sal
,MIN(salary) over (partition by dep_id) as min_sal
from employee)
select cte.dep_id,
max(case when salary=max_sal then emp_name end) as max_sal_emp
,max(case when salary=min_sal then emp_name end) as min_sal_emp
from cte
group by cte.dep_id

[@rajasharma9886]

select distinct dep_id,FIRST_VALUE(emp_name) over (partition by dep_id order by salary desc) maxx,
FIRST_VALUE(emp_name) over (partition by dep_id order by salary asc) minn
from employee

[@shravyasuvarna1229]

with class as (
select dep_id,salary,emp_name,row_number() over (partition by dep_id order by salary desc)
as min_emp,row_number() over (partition by dep_id order by salary asc)
as max_emp from employee_class)
select dep_id,max(case when max_emp=2 then emp_name else null end) as max_emp_name,
max(case when min_emp=2 then emp_name else null end )as min_emp_name
from class
group by dep_id

[@VISHALSINGH-jw2nn]

select dep_id,max(case when r1=1 then emp_name end) as emp_max_sal,
max(case when r=1 then emp_name end) as emp_min_sal from
(select *,row_number() over(partition by dep_id order by salary) as r,
row_number() over(partition by dep_id order by salary desc) as r1
from employee
)t
group by dep_id

[@akshayjain7937]

with cte as (
select *,ROW_NUMBER() over (partition by dep_id order by salary) as rn,
first_value(emp_name) over (partition by dep_id order by salary) as min_salary_name,
first_value(emp_name) over (partition by dep_id order by salary desc) as max_salary_name
from employee_new
)
select dep_id,min_salary_name,max_salary_name
from cte
where rn=1

[@mohdtoufique3786]

Hi Ankit...Thanks for the content...
My approach for the problem
1) WITH max_min_salary AS(
SELECT *,max(salary)OVER(PARTITION BY dep_id)AS max_salary,
min(salary)OVER(PARTITION BY dep_id)AS min_salary
FROM employee)
SELECT dep_id,max(CASE WHEN salary=max_salary THEN emp_name END) AS max_salary_employee,
max(CASE WHEN salary=min_salary THEN emp_name END) AS min_salary_employee
FROM max_min_salary
GROUP BY dep_id
2) WITH max_min_salary AS(
SELECT dep_id,max(salary)AS maximum_salary,min(salary) AS minimum_salary
FROM employee
GROUP BY dep_id)
SELECT e.dep_id,
max(CASE WHEN e.salary=maximum_salary THEN emp_name END) AS max_emp_name,
min(CASE WHEN e.salary=minimum_salary THEN emp_name END) AS min_emp_name
FROM employee e
INNER JOIN max_min_salary m ON e.dep_id=m.dep_id
GROUP BY e.dep_id;

[@prabhatgupta6415]

same way i did thnks

[@shankarpesala921]

with cte as (
select *,ROW_NUMBER() over (partition by dep_id order by salary asc) r1,
ROW_NUMBER() over (partition by dep_id order by salary desc) r2
from EMPLOYEen)
select * from
(select dep_id d,emp_name mins from cte where r1=1) a
join
(select dep_id d,emp_name maxs from cte where r2=1) b
on a.d=b.d

[@rahulmehla2014]

my approach:
with cte as
(select
*,
dense_rank() over(partition by dep_id order by salary desc) as h_rn,
dense_rank() over(partition by dep_id order by salary ) as l_rn
from employee)
select
dep_id,
max(case when h_rn = 1 then emp_name end) as emp_name_max_salary,
max(case when l_rn = 1 then emp_name end) as emp_name_min_salary
from cte
group by dep_id

[@vaibhavtiwari8670]

I am not able to understand why you used join
with cte as (select *,dense_rank()over(partition by dep_id order by salary desc) as rn from employee)
select dep_id, max(case when rn=1 then emp_name end )as highest_salary,
max(case when rn=2 then emp_name end )as lowest_salary
from cte
group by dep_id
this is really easy and less complex for getting the same output

[@dhrumilgohel1655]

Great video sir.
I have one doubt like is subquery is not good in sql as it might increase the time complexity of query execution ?
my first solution:
with cte as (select dep_id,emp_name as max_sal from employee
where (dep_id,salary) in (select dep_id,max(salary) from employee group by dep_id)),
cte2 as (select dep_id,emp_name as min_sal from employee
where (dep_id,salary) in (select dep_id,min(salary) from employee group by dep_id))

select c.dep_id,c.max_sal,cc.min_sal from cte c inner join cte2 cc on c.dep_id = cc.dep_id;

[@ansumansahoo8628]

Thank You So Much ! It will Help us a lot 👍👍

[@radhikagupta7314]

select emp_name,salary,dep_id,
case when rank1=1 and dep_id=1 then 'Lowest in Department 1'
when rank1=1 and dep_id=2 then 'Lowest in Department 2'
when rank1=count_emp and dep_id=1 then 'HIghest in Department 1'
when rank1=count_emp and dep_id=2 then 'Highest in Department 2'
end as "comment"
from (
select e.*,rank() over (partition by dep_id order by salary asc) as rank1,
count(1) over (partition by dep_id ) as count_emp
from employee e
)
where rank1=1 or rank1=count_emp

[@NikitaMate-i3f]

select dep_id,
max(case when low_rk=1 then emp_name end ) as low_salary_emp,
max(case when high_rk=1 then emp_name end )as high_salary_emp
from(
select *,rank() over(partition by dep_id order by salary ) as low_rk,
rank() over(partition by dep_id order by salary desc) as high_rk
from employee) a group by dep_id
;

[@Sambhavjain1301]

with high_salary as
(select *, Rank() over(partition by dep_id order by salary desc) as rnk from emp),
low_salary as
(select *, Rank() over(partition by dep_id order by salary asc) as rnk from emp)
select h.dep_id, h.emp_name, l.emp_name
from high_salary as h inner join low_salary as l on h.dep_id=l.dep_id where h.rnk=1 and l.rnk=1

[@nightoutwithnifty4734]

with cte as (
select emp_name,dep_id,salary,max(salary)over (partition by dep_id) as max_salary,
min(salary)over (partition by dep_id) as min_salary from employee)
select dep_id,
max(case when salary = max_salary then emp_name else null end) as max_sal_emp,
max(case when salary = min_salary then emp_name else null end) as min_sal_emp
from cte
group by 1

[@AmanDancetunesaman]

with cte as
(select emp_name,salary,dep_id,
dense_rank() over(partition by dep_id order by salary) as rnk from employee),
cte2 as (
select dep_id,case when rnk in (select min(rnk) from cte) then emp_name end as min_salary_employee_name,
case when rnk in (select max(rnk) from cte) then emp_name end as max_salary_employee_name
from cte)
select dep_id,max(max_salary_employee_name)as max_salary_employee_name ,max(min_salary_employee_name)as min_salary_employee_name from cte2
group by dep_id

[@shushantkumar8396]

this is my solution for this problem-
with cte as(
select dep_id,emp_name from(
select dep_id,emp_name,row_number() over(partition by dep_id order by salary desc) rn from employee
) tbl
where rn=1),
dte as (select dep_id,emp_name from(
select dep_id,emp_name,row_number() over(partition by dep_id order by salary asc) rn from employee
) tb
where rn=1)
select c.dep_id,c.emp_name as highest_salary_employee,d.emp_name as lowest_salary_employee from cte c join dte d
on c.dep_id=d.dep_id

[@tanyamoghe8928]

SELECT DISTINCT dep_id,
first_value(emp_name) OVER (partition By dep_id ORDER BY salary DESC ) as max_salary_emp,
last_value(emp_name) OVER (partition By dep_id ORDER BY salary DESC Range between unbounded preceding and unbounded following )
as min_salary_emp
FROM employee;

[@prabhatgupta6415]

Too good..
SELECT distinct dep_id,
first_value(emp_name) OVER (partition By dep_id ORDER BY salary DESC ) as max_salary_emp,
first_value(emp_name) OVER (partition By dep_id ORDER BY salary ASC)
as min_salary_emp from employee5

[@DurgaKeshari]

My solution is....
1) select dep_id,a.emp_name as emp_name_max_salary ,b.emp_name as emp_name_min_salary from
(select dep_id,emp_name from employee where salary in (select max(salary) from employee group by dep_id)) a
join
(select dep_id,emp_name from employee where salary in (select min(salary) from employee group by dep_id)) b
using(dep_id)
order by 1

[@vaishaliambasta1045]

I tried using 2 cte and it is done :
with max as (select dep_id, emp_name, salary from employee where salary in (select max(salary) from employee group by dep_id)),
min as(select dep_id, emp_name, salary from employee where salary in (select min(salary) from employee group by dep_id))
select m.dep_id, m.emp_name as emp_name_max_salary
,n.emp_name as emp_name_min_salary from max m join min n
on m.dep_id=n.dep_id;

[@gobindabehera5918]

select dep_id,
max(case when salary=salary then emp_name end) max_salary,
min(case when salary=salary then emp_name end) min_salary
from employee_7
group by dep_id

[@rajn2155]

select dep_id,max(case when dr=1 then emp_name end) as Emp_with_MAX_sal,
max(case when ar=1 then emp_name end )as Emp_with_MIN_sal
from(
select *,dense_rank() over(partition by dep_id order by salary desc) as dr
,dense_rank() over(partition by dep_id order by salary) as ar
from employee
) aa
group by dep_id

[@prudhvithotapalli6596]

Hello Ankit for the two methods you wrote the solution what would be the case when there are two employees with same highest/lowest salary. What changes you would make to display both of their names

[@roshangangurde7188]

Will use DENSE RANK () function instead of ROW_ NUMBER function.
As when ROW_ NUMBER function detects two identical values it assigns different row number to records
But DENSE RANK() assigns the same rank to all identical values and it also doesn't skip rank like RANK().

[@mohammedriyaz-g4s]

with cte as (select case when max_salary =1 then emp_name end max_sal_emp,
case when min_salary =1 then emp_name end min_sal_emp,dep_id from (select * , dense_rank() over (partition by dep_id order by salary desc) max_salary,
dense_rank() over (partition by dep_id order by salary asc) min_salary from employee))
select dep_id,max(max_sal_emp) as max_sal_emp,max(min_sal_emp)as min_sal_emp from cte group by dep_id

[@saiswaroop3570]

select dep_id,min(case when salary=min_sal then emp_name end) as min_sal,min(case when salary=max_sal then emp_name end) as max_sal from ( select emp_name,dep_id,salary,min(salary)over(partition by dep_id order by salary range between unbounded preceding and unbounded following) min_sal , max(salary)over(partition by dep_id order by salary range between unbounded preceding and unbounded following) max_sal from employee ) group by dep_id

[@vimalraja1639]

this also should work in oracle:
with cte as (select dep_id , max(salary) as max_sal, min(salary) as min_sal from
employee
group by dep_id)
select e.emp_name , e.dep_id, 'MIN_SAL' from employee e
where (e.DEP_ID, e.SALARY) in (select dep_id, min_sal from cte )
union
select e.emp_name, e.dep_id, 'MAX_SAL' from employee e
where (e.DEP_ID, e.SALARY) in (select dep_id, max_sal from cte);

[@ebrahimangolkar3497]

select distinct a.dep_id,b.emp_name as high,c.emp_name as low from pl.employee a
left join
(
select * from
(
select *,
max(salary) over (partition by dep_id) as rnk from pl.employee
)tq2
where salary = rnk
)b
on a.dep_id=b.dep_id
left join
(
select * from
(
select *,
min(salary) over (partition by dep_id) as rnk from pl.employee
)tq2
where salary = rnk
)c
on a.dep_id=c.dep_id;

[@arupchandra8602]

select a.dep_id,MAX(a.emp_name_max_sal) as emp_name_max_sal,MIN(a.emp_name_min_sal) as emp_name_min_sal
from(
select *,
case when salary=max(salary) over(partition by dep_id) then emp_name end emp_name_max_sal,
case when salary=min(salary) over(partition by dep_id) then emp_name end emp_name_min_sal
from employee)a
group by a.dep_id

[@Ankitatewary-q6w]

with cte as(
select *,
max(salary) over(partition by dep_id) max_sal,
min(salary) over (partition by dep_id) min_sal
from employee),
max_table as(
select dep_id,emp_name as max_sal_emp
from cte
where salary=max_sal),
min_table as(
select dep_id,emp_name as min_sal_emp
from cte
where salary=min_sal)
select max_table.*,min_table.min_sal_emp
from max_table join min_table on (max_table.dep_id=min_table.dep_id);

[@sushilkumarpatil2539]

with cte as
(select distinct dep_id,
(case when salary=max(salary) over(partition by dep_id) then emp_name end) as dep_max_salary,
(case when salary=min(salary) over(partition by dep_id) then emp_name end) as dep_min_salary
from employee)
select dep_id,max(dep_max_salary) as dep_max_salary,min(dep_min_salary) as dep_min_salary
from cte
group by
dep_id;

[@vamshireddy1183]

with new1 as (select dep_id,emp_name as max_sal_emp from
(select dep_id,emp_name,rank() over (partition by dep_id order by salary desc) as highranking
from employee) as A
where highranking = 1),
new2 as (select dep_id,emp_name as min_sal_emp from
(select dep_id,emp_name,rank() over (partition by dep_id order by salary) as lowranking
from employee) as A
where lowranking = 1)
select a.dep_id,max_sal_emp,min_sal_emp
from new1 a
join new2 b
on a.dep_id = b.dep_id;

[@khushishrivastava331]

Hello Ankit my approach to the problem is
;with cte as (select emp_name, max(salary) over(partition by dep_id) as max_sal,
min(salary) over(partition by dep_id) as min_sal
from employee
)
select e.emp_name, e.salary
from employee as e
join cte as c
on e.emp_name=c.emp_name and (e.salary=c.max_sal or e.salary=c.min_sal)
order by e.dep_id
let me know if its correct and we can use it
Thanks!

[@ankushjain4128]

I like your way of explanation ..can you guide to read execution plan which approach is more optimized like same have 2 approach ?

[@ankitbansal6]

Yes, sure

[@anshulgupta1611]

Thanks, i liked your approach. but i tried with join, please correct if this approach is not optimized one.
Select e.Dep_id,e.emp_name as MaxSalary, m.emp_name as MinSalary From (
(Select emp_name,Dep_id,salary From (
Select *, RANK() Over (partition by dep_id order BY salary Desc) as salaryRank
From employee) X
Where salaryRank =1) e
INNER JOIN
(Select emp_name,Dep_id,salary From (
Select *, RANK() Over (partition by dep_id order BY salary Desc) as salaryRank
From employee) X
Where salaryRank =2) m
ON e.dep_id = m.dep_id);

[@Pavas-n6j]

with cte1 as (select dep_id,emp_name, rank() over (partition by dep_id order by salary desc) as rnk from employee),
cte2 as (select dep_id,emp_name, rank() over (partition by dep_id order by salary) as rnk from employee)
select * from cte1 inner join cte2 on cte1.dep_id=cte2.dep_id where cte1.rnk=1 and cte2.rnk=1;

[@manishmr2116]

SIMPLE SOLUTION ! 🔥🔥🔥
select a.dep_id,b.emp_name as highest_salary,c.emp_name as lowest_salary
from
(select dep_id,max(salary) as max_salary,min(salary) as min_salary
from employee
group by dep_id) a
join
employee b on b.dep_id=a.dep_id and b.salary=a.max_salary
join
employee c on c.dep_id=a.dep_id and c.salary=a.min_salary

[@swamivivekananda-cyclonicm8781]

Thank you for your efforts

[@ankitbansal6]

My pleasure

[@Priyanka-us8rw]

Is that possible to have doc as well for every interview questions you are sharing in you videos

[@ritudahiya6223]

@ankit bansal Can you please explain what is significance of using min Or max before case when

[@ankitbansal6]

Watch this
th-cam.com/video/O6mDdUIvx9k/w-d-xo.html

[@sannigupta4042]

select b.emp_name as emp_name_max_salary ,d.emp_name as emp_name_min_salary,b.dep_id from
(select a.* from
(select *,
row_number() over (partition by dep_id order by salary desc) as rn
from Employee ) a
where a.rn = 1) b
join
(select c.* from
(select *,
row_number() over (partition by dep_id order by salary desc) as rn
from Employee ) c
where c.rn = 2) d
on b.dep_id = d.dep_id

[@JyotiYadav-gq1fs]

Can we make it easy with this query ----
with cte as
(
select *, RANK() over (partition by dep_id order by salary asc) as min_salary
from employee)
select * from cte where min_salary=1

[@meghnasoni]

My solution:
with cte as(select *,
max(salary) over( partition by dep_id order by salary desc) as mx,
min(salary) over( partition by dep_id order by salary ) as mn
from employee)
SELECT dep_id, max(case when salary = mx then emp_name end )as max_sal_emp,
max(Case when salary = mn then emp_name end )as min_sal_emp
FROM cte
GROUP by 1

[@aayushibirla2590]

very nice solution

[@ankitbansal6]

Thank you! Cheers!

[@p51_ishansingh49]

Using Join and Window Function
select t1.dep_id,t1.emp_name_max_salary, t2.emp_name_min_salary
from
(select dep_id,emp_name as emp_name_max_salary from
(select *, dense_rank () over(partition by dep_id order by salary desc) as rnk
from employee) tab1
where rnk=1) t1
join
(select dep_id, emp_name as emp_name_min_salary from
(select *, dense_rank () over(partition by dep_id order by salary) as rnk
from employee) tab1
where rnk=1) t2
on t1.dep_id=t2.dep_id;

[@arshadmohammed2346]

Can you explain in detail about min and max that are used along with case when?

[@mohammedriyaz-g4s]

with cte1 as (select * from (select dep_id, emp_name as max_salary,
dense_rank() over(partition by dep_id order by salary desc) max_sal_em from employee)a where a.max_sal_em=1),
cte2 as (select * from (select dep_id, emp_name as min_salary,
dense_rank() over(partition by dep_id order by salary ) min_sal_em from employee)a where a.min_sal_em=1)
select a.dep_id,a.min_salary,b.max_salary from cte2 a
inner join cte1 b on a.dep_id=b.dep_id

[@MohitKumar-jp5wy]

Both scenarios will fail if more than 1 employee gets (min/max) same salary, as more than 1 employee can get the department's min/max salary.

[@snowflaketrainingintelugu]

Just change the row number to rank if more employees has max or min sal

[@akshitdadheech9870]

Will this question be asked in for a data analyst fresher going for an interview? Also can you make a separate playlist for interview questions for Data Analyst Fresher according to you thanks 😅

[@anudeepgupa]

My way:
select distinct dep_id, first_value(emp_name) over(partition by dep_id order by salary desc) as max_sal_emp,
First_value(emp_name) over(partition by dep_id order by salary asc) as min_sal_emp
From employee
Note: ignore spelling errors as I am commenting from mobile

[@parmoddhiman678]

by window fxn
with cte as (SELECT * ,
max(salary) over(partition by dep_id) as max_salary,
min(salary) over(partition by dep_id) as min_salary
FROM employee)
select c.dep_id,
max(case when salary=max_salary then emp_name end ) as max_emp,
max( case when salary=min_salary then emp_name end ) as min_emp
from cte c
group by 1

[@Progressivethoughts]

Thankyou so much Ankit and there was one more table named department so instead of department id can we get department name from another table in same output

[@ankitbansal6]

Yes you need to just join with dep table and get dep name and select name instead of id

[@Progressivethoughts]

@@ankitbansal6 i'll try

[@prashansapunjabi]

I know that the following solution is not the most robust but it works for this table so here goes:
with cte as (select dep_id, max(salary) as high_sal, min(salary) as low_sal from employee group by dep_id)
select cte.dep_id, e1.emp_name, e2.emp_name from cte join employee e1 on cte.dep_id = e1.dep_id and cte.high_sal = e1.salary
join employee e2 on cte.dep_id = e2.dep_id and cte.low_sal = e2.salary;

[@ramakumarguntamadugu1299]

great content as always 👌👍 can you please make a video on SQL functions and stored procedures if possible

[@ankitbansal6]

Great suggestion!

[@kanikeveeresh4448]

select A.dep_id, A.emp_name as higher_Emp_salary,B.emp_name as Lowest_salary_emp from (
select dep_id,emp_name,salary from (
select *,row_number() over(partition by dep_id order by salary desc) as [rank] from employee_1 ) A where [rank]=1 ) as A
inner join (select dep_id,emp_name,salary from (
select *,row_number() over(partition by dep_id order by salary asc) as [rank] from employee_1 ) A where [rank]=1) as B
on A.dep_id=B.dep_id

[@guptaaniket4155]

SELECT DISTINCT(dep_id),
FIRST_VALUE(emp_name) OVER(PARTITION BY dep_id ORDER By salary RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS MIN_SALARY,
LAST_VALUE(emp_name) OVER(PARTITION BY dep_id ORDER By salary RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS MAX_SALARY
FROM employee

[@mathavansg9227]

with cte as (SELECT *,max(salary) over(partition by dep_id) as max_salary,
min(salary) over(partition by dep_id) as min_salary from employee)

SELECT c.emp_name,c.dep_id,c.salary,e.emp_name as highest_paid_employee,e1.emp_name as Low_paid_employee from cte as c inner join employee as e on c.max_salary=e.salary
inner join employee e1 on c.min_salary=e1.salary

[@anishchhabra6085]

my solution in MySQL (I solved without looking into the above solution):
with sal_rank as
(
select *,
rank() over(partition by dep_id order by salary desc) as rnk
from emp)
select dep_id,
max(case when rnk = 1 then emp_name end) as 'emp_name_max_salary',
max(case when rnk = (select count(*) from emp group by dep_id order by count(*) desc limit 1) then emp_name end) as 'emp_name_min_salary'
from sal_rank
group by dep_id;
Can anyone inform me if the solution is correct or not, I am getting the required answer for this scenario but I wanted to ask for any edge case where this might fail.

[@shalubharvat1705]

How can we get 1, last and 3rd purchase date of every customers??

[@rallabandivijayvijay5456]

;
with cte_x as (
select dept,max(salary) as max_salary,min(salary) as min_salary from salaries group by dept
)
select b.dept,b.emp_name as max_sal_emp_name,c.emp_name as min_sal_emp_name
from cte_x a
join salaries b on a.dept=b.dept and b.salary=a.max_salary
join salaries c on c.salary=a.min_salary and a.dept=c.dept

[@akashkonda8385]

with cte1 AS (
select dep_id, emp_name as emp_max_salary
from employee
where salary in (select max(salary) from employee group by dep_id)
),
cte2 AS
(select dep_id, emp_name as emp_min_salary
from employee
where salary in (select min(salary) from employee group by dep_id)
)
select a.*, b.emp_min_salary
from cte1 a join cte2 b
on a.dep_id = b.dep_id
order by a.dep_id
;

[@NamanSeth1]

I tried combination of both the approaches on my own @AnkitBansal :D Thanks for the great videos. Keep educating us.
My Solution:
with cte as(
select *,dense_rank() over (partition by dep_id order by salary desc) rank,
min(salary) over (partition by dep_id) min_salary
from employee
)
select dep_id,
max(case when rank=1 then emp_name end) as max_sal_emp ,
max(case when salary=min_salary then emp_name end) as min_sal_emp
from cte
group by dep_id

[@ankitbansal6]

Nice work!

[@vru5696]

I did asc and desc rank wise in 2 different cte's and doing inner join based on dept id and at last where rn1=1 and rn2=2

[@ankitbansal6]

Can you write the query

[@vru5696]

@@ankitbansal6 with cte as
(select *, row_number() over (partition by depid order by sal desc)
rn1 from emp),
cte2 as
(select *, row_number() over (partition by depid order by sal desc)rn2 from emp)
select cte.depid,cte.ename as ename_max_salary,
cte2.ename as ename_min_salary from cte2 inner join cte
on cte2.depid=cte.depid
where rn=1 and rn2=2

[@animeshchittora5693]

@@vru5696 Even I thought the same

[@TargaryenGaming-u3u]

without join : with cte as (
select *,max(salary) over (partition by dep_id) max_salary,min(salary) over (partition by dep_id) min_salary
from employeelnt)
select dep_id,max(case when max_salary = salary then emp_name end) max_sal_emp
,max(case when min_salary = salary then emp_name end) min_sal_emp
from cte
group by dep_id;