Eventbrite Interview Question: Given a table that has parent... | Glassdoor

Interview Question

Engineer Interview San Francisco, CA

Given a table that has parent's id and children id, write a

  query that return grandparents and grandchildrens
Tags:
sql
Answer

Interview Answer

2 Answers

0

Relational schema - p_name and c_name make a composite primary key
relation {p_name varchar, c_name varchar}

select t1.Grandchild, t2.Grandparent
from
(select c_name as Grandchild, p_name from relation
where p_name in
(select distinct(r.p_name) from
relation r join relation t
on r.p_name = t.c_name)) as t1
inner join
(select p_name as Grandparent, c_name from relation
where c_name in
(select distinct(r.p_name) from
relation r join relation t
on r.p_name = t.c_name)) as t2
on
t1.p_name=t2.c_name
order by t1.Grandchild;

zenyoda on Jan 31, 2017
0

select t1.Grandchild as Grandchild ,t2.Grandparent as Grandparent from
(select distinct(r.c_name) as Grandchild,r.p_name as parent from relation r join relation t on r.p_name = t.c_name) t1
,(select distinct(r.p_name) as Grandparent,r.c_name as parent from relation r join relation t on r.c_name = t.p_name) t2
where t1.parent=t2.parent;

Sweta on Apr 21, 2019

Add Answers or Comments

To comment on this, Sign In or Sign Up.