SQL Interview Question - Solution (Part - XV) |

#sql #education #sqlfunctions #dataanalyst #dataengineers
#MeanLifeStudies #sqlinterview #datascience #interview #dataanalystinterview
Here are my medium and Github page links mentioned below:
/ mahendraee204
github.com/mahendra204
Here are table creation and insertion statements:
-------------------------------------------------------------------------------
create table employee (
empid int,
empname varchar(50),
mgrid int,
salary int
);
insert into employee values
(1, 'mahendra', 2, 25000),
(2, 'mahi', 5, 20000),
(3, 'sam', null, 45000),
(4, 'dev', 1, 20000),
(5, 'dash', 3, 97000),
(6, 'pratik', 6, 30000),
(7, 'singh', 2, 40000),
(8, 'rao', 5, 80000);

[@Vaibha293]

amazing bro..

[@MeanLifeStudies]

Thank You.

[@dasubabuch1596]

Hi Sir, I tried with hierarchical query for this problem. Can you please check it once? with t as ( select empid,empname, prior empname as managername,salary,level from employee start with mgrid = 3 connect by empid = prior mgrid ),t1 as ( select avg(salary) as av_salary from t ) select av_salary, empname||'-'||managername as emp_mgr_pair from t1, t where managername is not null;

[@VARUNTEJA73]

with cte as( select distinct e.empid,t.mgrid,e.empname as mngname,t.empname as empname,e.salary+t.salary as totalsal from employee e join employee t on e.empid=t.mgrid where e.salary

[@mrsantho]

with cte1 as( select e.*,e1.empname as managername, e1.salary as managersalary, (e.salary + e1.salary)/2 as averagesalary from employeet1 e join employeet1 e1 on e.mgrid=e1.empid ), cte2 as( select concat(empname,':',managername) as emp_mgr_pair, averagesalary as salary, dense_rank() over(order by averagesalary desc) as rn from cte1 ) select emp_mgr_pair,salary from cte2 where rn=2;

[@maheshnagisetty4485]

select mgr_emp,avg_sal from ( select *,rank() over(order by avg_sal desc) as rn from ( select concat(e.empname, ':', m.empname) as mgr_emp, (e.salary+m.salary)/2 avg_sal from employee as e join employee as m on e.mgrid=m.empid )as a ) as b where rn=2

[@Naveen-uz4hw]

select concat(manager_name,':',b.empname) as emp_mgr_name ,(manager_salary+salary)/2 as salary from (select empid as manager_id ,empname as manager_name ,salary as manager_salary from employee where mgrid is null)a join employee b on a.manager_id=b.mgrid Can you please validate this solution

[@MeanLifeStudies]

Yes. It is correct. But you are making it too complex. Kindly understand if a manager is not null for Sam then? I mean for Sam is any other manager then?