Double Self Join in SQL | Amazon Interview Question | Excel Explanation Included | Data Analytics

In this video we are going to discuss on SQL interview problem asked in Amazon interview for Business Analyst position. This question will be solved using double SQL self join concept.

Zero to hero(Advance) SQL Aggregation:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

script:
create table emp(
emp_id int,
emp_name varchar(20),
department_id int,
salary int,
manager_id int,
emp_age int);

insert into emp
values
(1, 'Ankit', 100,10000, 4, 39;
insert into emp
values (2, 'Mohit', 100, 15000, 5, 48);
insert into emp
values (3, 'Vikas', 100, 12000,4,37);
insert into emp
values (4, 'Rohit', 100, 14000, 2, 16);
insert into emp
values (5, 'Mudit', 200, 20000, 6,55);
insert into emp
values (6, 'Agam', 200, 12000,2, 14);
insert into emp
values (7, 'Sanjay', 200, 9000, 2,13);
insert into emp
values (8, 'Ashish', 200,5000,2,12);
insert into emp
values (9, 'Mukesh',300,6000,6,51);
insert into emp
values (10, 'Rakesh',500,7000,6,50);

#sql #dataengineer #amazon

[ankitbansal]

Do like the video so that it reaches maximum people.

[avi]

Explanation in Excel did the whole trick, thanks a lot Ankit Sir!

[sharu]

Thank you Ankit for taking the time in making this video and explaining it in a clutter-free manner 😇

[mantisbrains]

with cte as
(select e1.emp_id as employee_id, e1.emp_name as employee_name, e1.manager_id as manager_id, e2.emp_name as manager_name
from emp e1 left join emp e2
on e1.manager_id = e2.emp_id)
select c1.employee_id as emp_id, c1.employee_name as employee_name, c1.manager_name as manager_name, c2.manager_name as senior_manager_name
from cte c1 left join cte c2 on
c1.manager_id =c2.employee_id
order by emp_id

Thanks, Ankit! really a very good problem!

[chintuy]

Thanks Ankit for posting a video on my request are doing a great job by sharing knowledge..

[gouravkumar]

Amazing explanation Ankit bhai love this

[prashantmhatre]

Bhai, kya explain karte ho aap, so nice. Thanks a lot .

[overgoer]

really nice and clear explanation! How can we make it recursive? for example we have multiple level of managers, the top manager is level 0 and bottom employee is level 10 (for example) can we use some function to display all list of employees from the employee to top manager?

[GautamKumar-cirz]

You are great Ankit :) , because of you I have cleared many SQL concepts. Thanks a lot for these amazing videos.

[anirvansen]

MYSQL Solution


select t1.emp_id, t1.emp_name, t2.emp_name as manager_name, t3.emp_name as senior_manager
from emp t1 left join emp t2
on t1.manager_id = t2.emp_id
left join emp t3
on t2.manager_id = t3.emp_id

[ecubewithme]

Name source table as a, b, c initially, ( then self join/join table a with b on a. Manager_id=b.emp_id and keep this in cte ) as ab
Now again join table c with cte on ab. Manager_id=c.emp_id this gives final table with emp with their manager, nd senior manager 🙌😊

[bhaireviewkarelive]

This is Awesome Ankit. I just started learning sql and from your sessions I got to know lot of new things without any doubts, and I feel so comfortable now.😊

Thank you for your great sessions and helping us 👏

[marioedy]

It’s cool you explain in excel, it is very clear that way. Thank you

[anupgupta]

Hi Ankit Bhai can you use Azure data studio it comes with ssms , in Azure data studio we can see data more clearly and as always nice explanation

[reshmashaik]

with cte as(
select a.*,
case when a.manager_id=b.emp_id then b.emp_name end as man,
case when a.manager_id=b.emp_id then b.manager_id end as senior
from emp a, emp b )
select distinct a.emp_name, man as manager,
max(case when a.senior=b.emp_id then b.emp_name end) as snior_man
from cte a, emp b
where man is not null
group by a.emp_name, man

[vigneshnagaraj]

Please do post videos related to string functions in sql

[ethyria]

SELECT a.emp_id as emp_id, a.emp_name as emp_name, b.emp_name as manager, c.emp_name as sr_manager
from emp1 a, emp1 b, emp1 c
where b.emp_id = a.manager_id
and c.emp_id = b.manager_id

[angelnadar]

Thanks for making such informative and helpful videos!

[amanbhardwaj]

with cte as (
select e1.emp_id, e1.emp_name, e1.manager_id, e2.emp_id, e2.emp_name as manager, e2.manager_id,e3.emp_id,e3.emp_name as senior_manager, e3.manager_id from emp e1, emp e2, emp e3
where e1.manager_id= e2.emp_id and e2.manager_id= e3.emp_id
)
select emp_id, emp_name, manager, senior_manager
from cte

[vijay.s-llyq]

select a.emp_id, a.emp_name, b.emp_name As manager_name, c.emp_name as senior_manager_name from
emp A inner join emp b
on a.manager_id =b.emp_id
inner join emp c
on b.manager_id =c.emp_id