Split Hierarchy - SQL Interview Query 12 | SQL Problem Level "HARD"

[@malcorub]

This one was over my head, I need to work on my recursive cte understanding. At any rate I always learn from your videos even when i don't fully understand the final solution.

[@vikramjitsingh6769]

I think u used a lengthy and confusing approch- my soln would be - with recursive cte as (
select employee ,manager, row_number() over () as x from company where manager = (select employee from company where manager is null)
union all
select d.employee, d.manager, x from cte c join company d on c.employee = d.manager )
select concat('team',x) as teams, group_concat(manager separator ',') as members from (select manager , x from cte union select employee,x from cte
)y group by x order by x;

[@AmanRaj-p8w]

Great solution, I am thinking to solve this without using recursive function as I know for small hierarchy self join would work but there is too many hierarchy it doesn't. What is your thought in this?

[@muhammadabbas6645]

bro to be honest I was trying to solve using the video method but having trouble and found your solution it was very easy to understand thanks bro

[@muhammadabbas6645]

@vikramjitsingh6769 bro I have done just like yours but modify few things can you please tell me is that okay regards
with recursive cte as (
select employee ,manager, concat('team ',row_number() over (order by employee)) as rn
from company
where manager = (select employee from company where manager is null)

union all

select d.employee,d.manager,rn
from cte
join company d
on cte.employee = d.manager)

,final_cte as (
select
rn as teams,
string_agg(employee,',') as emp
from cte
group by rn
)
select
teams,
concat(employee,',',emp) as MEMBERS
from company c
cross join
final_cte
where c.manager is null
ORDER BY teams

[@vikramjitsingh6769]

@@muhammadabbas6645 yep it works 👍

[@sivakrishnasriram4782]

you did a great job bro👍.

[@shivinmehta7368]

with recursive cte as (
select manager,employee,row_number() over (order by employee) as rn from company
where manager=(select employee from company where manager is null)
union
select b.manager,b.employee, rn from cte join company b on cte.employee=b.manager

)
select concat ('Team ', rn),concat((select employee from company where manager is null),',',string_agg(employee,',')) from cte group by 1
order by 1

[@Parthasarathy.S-qi8vy]

Mysql solution:
with recursive cte as (select employee, manager, concat("Team ", row_number() over (order by employee) ) as team from company
where manager in (select employee from company where manager is null)
union all
select co.employee, co.manager, cte.team from cte join company co
where cte.employee = co.manager),
cte2 as (select team,(select employee from company where manager is null) as manager, group_concat(employee) as people from cte
group by team)
select Team,concat(manager,",",people) as people from cte2;

[@fathimafarahna2633]

As always best 😊

[@charanteja6808]

Toughest and most confusing one so far.

[@splendidabhi]

Thankyou so much

[@jojohoney6246]

In DB2 joins are not allowed inside recursive cte

[@MdZeeshan-m9u]

Thank you so much sir

[@SujathaAhilan-bj4qe]

Can you please provide sql server solution

[@kanappilly]

I modified this query to sql server ,
but error message says Msg 462, Level 16, State 1, Line 1
Outer join is not allowed in the recursive part of a recursive common table expression 'cte'. How to resolve this?

[@sam02109]

This is scary for me

[@rohit_vora]

here is my solution
with recursive cte as
(select 'team'||row_number()over(order by c2.employee) as teams, c1.employee e1, c2.employee e2 from company c1
join company c2 on c1.employee = c2.manager
where c1.manager is null),
cte2 as
(select teams, e1, e2
from cte
union
select cte2.teams,cte2.e1, c3.employee e2
from cte2
join company c3 on cte2.e2 = c3.manager),
cte3 as
(select teams, e1,string_agg(e2,',') members from cte2
group by 1,2)

select teams, (e1||','||members) as team from cte3
order by 1

[@Damon-007]

Sir the above solution works only when we add new teams in the table, if there is another hierarchy it will not give results. I write solution of above query it might works for that problem too team(please correct me sir if I'm wrong)
MS Sql
--new hierarchy
insert into company values ('david',null);
insert into company values ('bill','david');
select * from company;
with root as(
select employee from company
where manager is null)
,
teams as(
select concat('team ',row_number() over ( partition by root.employee order by (select null)),' of_',root.employee) team
, c.employee, root.employee as manager
from company c
inner join
root
on
root.employee=c.manager)
,rec as(
select team,employee,manager from teams
union all
select rec.team, c.employee, c.manager
from rec
inner join company c
on
rec.employee=c.manager
)
select team, string_agg(employee, ',') members
from
(select team, employee from rec
union
select team,manager from rec) k
group by team
Order by team;

[@yi-lehung6311]

This problem is so hard, I didn't think about the recursive... Instead, I used multiple cte to solve the problem haha

[@challajeevan4649]

what is the solution we made 200 tables updated in sql server replication got blocks

[@pveeranjireddy8959]

It looks difficult to understand Thoufiq.

[@jjayeshpawar]

Shared dataset is older, please provide for this query

[@shashanktiwari133]

scroll down to the bottom of the page and you will see problem 12 dataset link there

[@techTFQ]

sorry my bad, have updated the link now. plz check

[@techTFQ]

Have updated the correct link now

[@AdityaKumar-qi9ed]

this solution just bounced right over my head

[@Rameshkumar-dk8me]

You have made with great logic, I revised 2 times to get the logic... but this problem is very challenging

[@vishnugottipati9373]

plz provide ms sql server solution

[@amartyakumarsaha338]

19:16 use of Coalesc() is not clear to me.

[@CebuProvince]

Hi, your source code brings following error, do you check the source code before you offer this to the public?
sg 102, Level 15, State 1, Procedure spChallange#12, Line 25 [Batch Start Line 7]
Incorrect syntax near 'cte'.
Msg 102, Level 15, State 1, Procedure spChallange#12, Line 39 [Batch Start Line 7]
Incorrect syntax near ','.

[@techTFQ]

Do you think I post garbage script to public ?
If you have followed my videos, you would know that the scripts given is for PostgreSQL database and I had mentioned in a previous video when it comes to recursive query in MS SQL Server database, you need to make slight syntactical changes.

[@naveendevihosur8050]

plz provide class 12 th dataset, the shared dataset is of previous class

[@techTFQ]

have updated the link now. plz check

[@harishbagran6052]

🤯🤯

[@7vensandy_Data_Analyst]

with cte as (
SELECT c1.employee as E1, c2.employee as E2,c3.employee as E3,c3.manager as M1
FROM company c1
cross join company c2 on c1.manager=c2.employee
cross join company c3 on c2.manager=c3.employee AND C1.EMPLOYEE NOT IN (select distinct MANAGER from company WHERE MANAGER IS NOT NULL)
),
CTE2 AS (select group_concat(E1) AS EE,E2,E3,M1
from cte
GROUP BY E2,E3,M1)
SELECT CONCAT("Team ",row_number() OVER()) as Teams
,CASE WHEN M1 IS NULL THEN
CONCAT(e3,",",e2,",",EE)
else concat(m1,",",e3,",",e2,",",EE)
end AS MEMBERS
FROM CTE2
ORDER BY LENGTH(MEMBERS) desC;

[@DEEPAK-jx5si]

Here is my solution
with cte as(
SELECT c1.*,
c2.manager as second_manager
FROM company c1
join company c2
on c1.manager = c2.employee), cte2 as (
select cte.*,
c.manager as third_manager from
cte join company c
on cte.second_manager = c.employee),cte3 as(
select STRING_AGG(employee,',') employees, manager, second_manager,third_manager
from cte2
group by manager, second_manager,third_manager)
,cte4 as(
select employees, manager, second_manager,coalesce(third_manager,'') third_manager from cte3
where cte3.employees not in (select manager from cte3))
select concat('Team ',row_number() over(order by manager)) Teams,
concat(third_manager,',',second_manager,',', manager,',',employees) as
members from cte4

[@ShirleyShi-zb5us]

with recursive cte as (
select two_level.employee_name, root.employee_name as manager_name,
concat('Teams ',row_number()over(order by two_level.employee_name) )as teams,
1 as level
from public.employee_manager root
join
public.employee_manager two_level
on root.employee_name = two_level.manager_name
where root.manager_name is null

union
select
all_emp.*,cte.teams,cte.level+1 as level

from public.employee_manager all_emp
join
cte
on all_emp.manager_name = cte.employee_name


)
select teams,string_agg(members,',') as members_list from (
select teams,
case when level=1 then concat_ws(',',manager_name,employee_name)
else concat_ws(',',employee_name)
end as members
from cte
)a
group by teams
order by teams

[@Alexpudow]

ms sql approach
with a as (
select c2.employee, c2.manager, 1 hier, ROW_NUMBER() over(order by c2.employee) team
from company c1 join company c2 on c2.manager=c1.employee
where c1.manager is null
union all
select company.employee, company.manager, hier+1 hier, a.team
from a join company on a.employee=company.manager
),
b as (
select employee, team, ROW_NUMBER() over(partition by team order by hier) rn
from (
select manager employee, team, 0 hier
from a
where hier = 1
union all
select employee, team, hier
from a
) t )
select concat('team',team) team, string_agg(employee, ',') WITHIN GROUP (ORDER BY rn) members
from b
group by team

[@gphanisrinivasful]

Great approach! Liked how you added root node to all 3 teams in cte b.