Google Interview Question
Developer Program EngineersCountry: United States
Interview Type: In-Person
create table #Parent (Id INT, Age int, Child_Id int)
create table #Child (Id INT, Age int, Parent_Id int)
INSERT INTO #Parent VALUES (1, 55, 1)
INSERT INTO #Parent VALUES (1, 55, 2)
INSERT INTO #Parent VALUES (1, 55, 3)
INSERT INTO #Child VALUES(1, 30, 1)
INSERT INTO #Child VALUES(2, 28, 1)
INSERT INTO #Child VALUES(3, 25, 1)
INSERT INTO #Parent VALUES (2, 45, 4)
INSERT INTO #Parent VALUES (2, 45, 5)
INSERT INTO #Parent VALUES (2, 45, 6)
INSERT INTO #Child VALUES(4, 20, 2)
INSERT INTO #Child VALUES(5, 18, 2)
INSERT INTO #Child VALUES(6, 15, 2)
;WITH T AS
(SELECT p.Id
,FIRST_VALUE(c.Id) OVER(PARTITION BY p.Id ORDER BY c.Age asc) YoungestChild
,FIRST_VALUE(c.Id) OVER(PARTITION BY p.Id ORDER BY c.Age desc) EldestChild
,ROW_NUMBER() OVER(PARTITION BY p.Id ORDER BY c.Age desc) r
FROM #Parent p
INNER JOIN #Child c
ON p.Child_Id = c.Id)
SELECT Id, YoungestChild, EldestChild FROM T WHERE r = 1
DROP TABLE #Parent
DROP TABLE #Child
Table 1: Parents -> (int id, int age, int Child_id)
Table 2: Children -> (int id, int age, int parent_id)
Get the parent id, his/her oldest and youngest children ids
select parents.id, children.age from
parents p inner join children c on p.id = c.parent_id
where children.id in (select max(id), min(id) from children group by age)
select p.id, c.id from Parents p inner join Children c on p.id = c.parent_id where p.id = (select parent_id from children where age in (select max(age), min(age) from children group by parent_id));
- Sourabh Das June 02, 2014