SQL Interview Question | Find the name of manager having max no of employee working under him/her

In this video , we are going to learn the concept of self join
drop table emp
-- Create the emp table
CREATE TABLE emp (
emp_id INT PRIMARY KEY,
fname VARCHAR(50),
lname VARCHAR(50),
mgr_id INT
);
-- Insert sample data into the emp table
INSERT INTO emp (emp_id, fname, lname, mgr_id) VALUES
(1, 'John', 'Doe', NULL),
(2, 'Jane', 'Smith', 1),
(3, 'Michael', 'Johnson', 1),
(4, 'Emily', 'Williams', 2),
(5, 'Robert', 'Brown', 2),
(6, 'Sarah', 'Jones', 3),
(7, 'David', 'Davis', 3),
(8, 'Jessica', 'Miller', 4),
(9, 'William', 'Wilson', 4),
(10, 'Amanda', 'Taylor', 5),
(11, 'Sanjay', 'Bedwal', 4);
with mgr_cte
as
(
select mgr_name,count(1) emp_head_count from
(
select
e1.fname+' '+ e1.lname emp_name , coalesce( (e2.fname+' '+ e2.lname) , 'Not reporting to any manager' ) as mgr_name
from emp e1
left join emp e2
on e1.mgr_id=e2.emp_id
)t
group by mgr_name
)
select * from mgr_cte where emp_head_count in ( select max(emp_head_count) from mgr_cte)
select * from emp