SQL Interview Question - Solution (Part - XV) | #interview #Data Engineer #sq interview

#sql #education #sqlfunctions #dataanalyst #dataengineers
#MeanLifeStudies #sqlinterview #datascience #interview #dataanalystinterview
Here are My profiles that will definitely help your preparation for data analyst or data engineer roles.
Medium: medium.com/@mahendraee204
Github: 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.

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

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

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

[@VijayKumar-bx2ov]

with cte as ( select e2.empname, e1.empname as mgrname, e2.salary as empsal, e1.salary as mgrsal from employee e1 join employee e2 on e1.empid = e2.mgrid) select concat(empname,':',mgrname),(empsal + mgrsal) / 2 as salary from cte order by 2 desc offset 1 limit 1

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