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

30DaySQLQueryChallenge is a series of 30 videos covering 30 SQL Interview Queries. This is the TWELVE video in this series. This video series aims to provide 30 SQL Queries that can be asked during SQL Interviews. I will explain how to solve and address such SQL queries during interviews in these videos.

Let's follow the below routine to make the best use of it:
1. Watch the YouTube video (first half) to understand the problem statement.

2. Go to my discord server (link below), download the dataset for each problem, and try solving it yourself.

3. Share your solution on Discord and discuss different solutions and issues on my Discord server.

4. Watch the second half of my YouTube video to find my solution to the problem.

5. Share it with your contacts and spread the knowledge.

DOWNLOAD the Dataset from below:

Timeline:
00:00 Intro
00:10 Understanding Problem Statement
02:02 Solution to the SQL Problem

Thanks for participating in this challenge!

Good luck and Happy Learning!

[vikramjitsingh]

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;

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

[Rameshkumar-dkme]

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

[Parthasarathy.S-qivy]

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;

[AdityaKumar-qied]

this solution just bounced right over my head

[shivinmehta]

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

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

[challajeevan]

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

[yi-lehung]

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

[SujathaAhilan-bjqe]

Can you please provide sql server solution

[atifsuhail]

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;

[amartyakumarsaha]

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

[charanteja]

Toughest and most confusing one so far.

[naveendevihosur]

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

[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 ', '.

[pveeranjireddy]

It looks difficult to understand Thoufiq.

[jjayeshpawar]

Shared dataset is older, please provide for this query

[DEEPAK-jxsi]

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 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-zbus]

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