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;

[@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

[@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

[@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

[@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 😊

[@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

[@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

[@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

[@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

[@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

[@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.

[@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

[@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 ❤

[@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;

[@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

[@avi8016]

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

[@ankitbansal6]

Glad you enjoyed it

[@KapilKumar-hk9xk]

wow, alway mind opening yet simple solutions.

[@DEwithDhairy]

PySpark version of this problem with little twist: kzbin.info/www/bejne/gabZkJdrqt6Vf7csi=_1KLGDMhIPXCB52f

[@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

[@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

[@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

[@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

[@sowmya6471]

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

[@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;

[@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

[@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.

[@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;

[@dfkgjdflkg]

This is great content. such an amount of informatiion.

[@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

[@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

[@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

[@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

[@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

[@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

[@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

[@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.

[@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

[@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

[@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

[@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

[@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

[@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

[@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

[@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

[@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

[@pr-rb6jl]

Thanks Ankit ...Superb

[@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 ;

[@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

[@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;

[@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

[@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

[@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

[@ansumansahoo8628]

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

[@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;

[@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

[@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

[@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

[@prajjwaljaiswal3419]

I opted 2nd approach.

[@ankitbansal6]

👍

[@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

[@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

[@Chathur732]

with cte as ( select *, dense_rank() over(partition by dep_id order by salary ) as DR from employee_LT order by dep_id ), cte_2 as ( select dep_id, max(dr) as max_dr , min(dr) as min_dr from cte group by dep_id ) select c1.dep_id, max(case when dr = max_dr then emp_name end) as max_emp_name, max(case when dr = min_dr then emp_name end) as min_emp_name from cte c1 left join cte_2 c2 on c1.dep_id = c2.dep_id group by c1.dep_id order by c1.dep_id MY 2nd method: select dep_id,max(case when dr_desc = 1 then emp_name end) as max_sal_emp, max(case when dr_asc = 1 then emp_name end) as min_sal_emp from ( select * , dense_rank() over(partition by dep_id order by salary) as DR_asc, dense_rank() over(partition by dep_id order by salary desc ) as DR_desc from employee_LT) group by dep_id

[@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

[@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);

[@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

[@Priyanka-us8rw]

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

[@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

[@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!

[@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

[@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;

[@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;

[@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

[@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);

[@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().

[@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;

[@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;

[@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);

[@arshadmohammed2346]

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

[@shalubharvat1705]

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

[@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

[@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;

[@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

[@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;

[@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

[@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 😅

[@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

[@syedzeeshan4742]

WITH cte AS(SELECT *,rank() over(PARTITION BY dep_id ORDER BY salary) AS rnk,rank() over(PARTITION BY dep_id ORDER BY salary desc) AS raank FROM employee1) SELECT dep_id,min(CASE WHEN rnk=1 THEN emp_name END) AS min_sal, min(CASE WHEN raank=1 THEN emp_name END) AS max_sal FROM cte GROUP BY dep_id

[@ritudahiya6223]

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

[@ankitbansal6]

Watch this kzbin.info/www/bejne/hWfQdZeLfturb80

[@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

[@aayushibirla2590]

very nice solution

[@ankitbansal6]

Thank you! Cheers!

[@nirmalbisht9710]

My Solution: select AA.dep_id ,max(case when AA.l=AA.salary then AA.emp_name else null end ) as l_salary_na, max ( case when AA.h=AA.salary then AA.emp_name else null end ) as h_salary_na from ( select dep_id,emp_name,salary ,min(salary) over(partition by dep_id) as l ,max(salary) over(partition by dep_id) as H from employee ) As AA group by AA.dep_id;

[@RahulYadav-le8cr]

select dep_id, (select emp_name from employee e1 where e1.salary = max_dep_sal and e1.dep_id = dep_id limit 1) max_dep_sal, (select emp_name from employee e1 where e1.salary = min_dep_sal and e1.dep_id = dep_id limit 1) min_dep_sal from ( select dep_id,max(salary) max_dep_sal,min(salary) min_dep_sal from employee group by dep_id )tmp;

[@subhojitchatterjee6312]

My approach: WITH CTE AS ( SELECT *,MAX(SALARY) OVER(PARTITION BY DEP_ID) AS DEP_MAX ,MIN(SALARY) OVER(PARTITION BY DEP_ID) AS DEP_MIN FROM EMPLOYEE) SELECT DEP_ID,GROUP_CONCAT(CASE WHEN SALARY=DEP_MAX THEN EMP_NAME END) AS EMP_MAX_SAL, GROUP_CONCAT(CASE WHEN SALARY=DEP_MIN THEN EMP_NAME END) AS EMP_MIN_SAL FROM CTE GROUP BY 1;

[@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

[@Arnob_111]

What if I want to display the individual salary of max_sal_emp and min_sal_emp alongside?

[@Alexpudow]

These won't work if you have two or more equal max or min salaries in one departmint🙂

[@MonkeyDLuffy4885]

My Approach: WITH cte AS ( SELECT emp_name, dep_id, salary, DENSE_RANK() OVER(PARTITION BY dep_id ORDER BY salary DESC) AS dept_rank_salary FROM employee ) SELECT dep_id, MAX(CASE WHEN dept_rank_salary = (SELECT MIN(dept_rank_salary) FROM cte AS c2 WHERE c2.dep_id = c1.dep_id) THEN emp_name END) AS emp_name_max_salary, MAX(CASE WHEN dept_rank_salary = (SELECT MAX(dept_rank_salary) FROM cte AS c3 WHERE c3.dep_id = c1.dep_id) THEN emp_name END) AS emp_name_min_salary FROM cte AS c1 GROUP BY 1