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