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👍.

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

[@fathimafarahna2633]

As always best 😊

[@splendidabhi]

Thankyou so much

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

[@MdZeeshan-m9u]

Thank you so much sir

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

[@yi-lehung6311]

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

[@jojohoney6246]

In DB2 joins are not allowed inside recursive cte

[@harishbagran6052]

🤯🤯

[@charanteja6808]

Toughest and most confusing one so far.

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

[@SujathaAhilan-bj4qe]

Can you please provide sql server solution

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

[@challajeevan4649]

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

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

[@sam02109]

This is scary for me

[@vishnugottipati9373]

plz provide ms sql server solution

[@naveendevihosur8050]

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

[@techTFQ]

have updated the link now. plz check

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

[@pveeranjireddy8959]

It looks difficult to understand Thoufiq.

[@Löwen_av]

MS SQL: WITH cte_teams AS ( SELECT mng.employee, CONCAT('Team ', ROW_NUMBER() OVER (ORDER BY mng.employee)) AS teams FROM company root JOIN company mng ON root.employee = mng.manager WHERE root.manager IS NULL ), cte AS ( SELECT c.employee, c.manager, t.teams FROM company c JOIN cte_teams t ON c.employee = t.employee UNION ALL SELECT c.employee, c.manager, cte.teams FROM company c JOIN cte ON c.manager = cte.employee ) SELECT teams, (SELECT employee FROM company WHERE manager IS NULL) + ', ' + STRING_AGG(employee, ', ') AS members FROM cte GROUP BY teams ORDER BY teams;

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

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

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

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