Question of the day

Hi folks, I got a question from my junior in my organization-


Input the name of a person born in Generation X and output the names of all of their descendants born in Generation Z, but only if these descendants are brother and sister (have the same parent)

/*
Create table Test (id int ,name varchar(100),Parent_ID int null,Generation varchar(1),Gender varchar(6))

Insert into test (id,name,Parent_ID,Generation,Gender)
select 1,'John',null,'x','male'
union
select 2,'Jill',1,'y','Female'
union
select 3,'Andy',2,'z','male'
union
select 4,'Ralph',2,'z','male'
union
select 5,'Lisa',null,'x','Female'
union
select 6,'Steve',5,'y','male'
union
select 7,'Sean',6,'y','male'
union
select 8,'Sarah',6,'y','Female'
union
select 9,'Emily',7,'z','Female'
union
select 10,'Matt',7,'z','male'

Drop table test
*/

select * from Test




If input is lisa

Then output should be:
 

-------------
Answer :




Declare @name varchar(50)='Lisa'

;with cte
as
(
Select id,name,Parent_id,generation,gender from test where name =@name and generation='x'
Union All
Select t.id, t.name,t.Parent_id,t.generation,t.gender from test t
join cte c on c. id=t.parent_id
)
,condition
as
(
select Parent_id,COUNT(*) cnt  from cte Where generation='z' Group by parent_id having COUNT(*)>1
)
,sibling
as
(
select parent_id,Count(gender) cntg from (select distinct c.parent_id,c.gender from cte c join condition co on co.parent_id=c.parent_id) a group by parent_id having count(gender)>1
)
select c.name from cte c join sibling s on c.parent_id=s.parent_id