SQL Interview Question | Capgemini | Data Engineer

[@shankarshiva5587]

informative video:
optimal way:
Select M. id as mgrid, M.name as mgrname,COUNT(*) as No_Of_Employees
From Employees E inner join employees M
on E.managerid=M.id
Group by M.id, M.name
Having count(*)>4

[@anuragshrivastava7855]

with cte as (select *, rank() over(partition by managerid order by id) as rnk from Employee)
select distinct managerid from cte where rnk>4 and managerid is not null

[@codinglake]

nice one

[@rajakumarkeelu9449]

@@codinglake well done, I am still thinking the usage of DISTINCT here. See if I want to query this, rest is same
WITH CTE AS (
SELECT *, RANK() OVER (PARTITION BY ManagerID ORDER BY id) AS R_NK
FROM Employee
)
SELECT ManagerID
FROM CTE
WHERE R_NK >4
AND ManagerID IS NOT NULL;
so just to understand is there any specific reason for using DISTINCT as I am eager to learn that if that is something I never seen before

[@shanupandey5932]

select e1.name,count(e2.managerId) as cnt from employee e1,employee e2
on e1.id=e2.managerId
group by e1.name
having count(e2.managerId) >4

[@sovon9]

select name from employees where id in (select managerid from employees group by managerid having count(managerid)>4);

[@codinglake]

Nice one 👍

[@VonMellArevalo]

I tried your query but the output is not accurate.

[@KRANTHIKUMARMARADA]

select count(e.managerid),m.name from employes e join employes m
on e.managerid=m.id group by m.name having count(e.managerid)>4

[@Bharatkumar-gh2um]

You can write without join having(emp)>2 by use grouping

[@codinglake]

You can write many ways, but in this video i have to show step by step process

[@rajakumarkeelu9449]

SELECT Managerid, staff FROM( SELECT ManagerID, COUNT(name) AS staff FROM Employee GROUP BY ManagerID) AS Subquery WHERE staff >4;

[@gublu123]

Can anybody explain why are we even using partition by??

[@codinglake]

watch this.
ua-cam.com/video/OkupQBY8yXw/v-deo.html

[@rajakumarkeelu9449]

SET @Query = CONCAT('SELECT Manager_id, COUNT(name) FROM Employee GROUP BY Manager_ID
HAVING COUNT(name) >', @Value);
SET @Value =4;
PREPARE stmt FROM @Query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

[@vivekshirsat9249]

my solution is below
select
e2.name as manager_name,
COUNT(e1.managerId) as no_of_employees
from Employee as e1
join Employee as e2
on e1.managerId = e2.id
group by e2.name
having COUNT(e1.managerId) > 4

[@rajakumarkeelu9449]

WITH Manager4 AS (
SELECT ManagerID, COUNT(name) AS staff FROM Employee GROUP BY ManagerID
)
SELECT ManagerID, staff, RANK() OVER(ORDER BY staff_count DESC) AS Finding_Manager4
FROM Manager4
WHERE staff > 4;

[@tubelessmoto]

Concept clear.

[@computerkefude]

Nice explain

[@codinglake]

Thanks!

[@arjunarjun-f3u6j]

sir its hard

[@codinglake]

not hard, practice more.

[@rajakumarkeelu9449]

SELECT e.managerid FROM employee e
JOIN (SELECT managerid, COUNT(*) AS Employee_Count
FROM employee
GROUP BY managerid
HAVING COUNT(*) > 4) AS Subquery
ON e.managerid = Subquery.managerid
GROUP BY e.managerid;