SQL Interview Question - Solution (Part - XV) |

#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: / 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.

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

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

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

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

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