Infibeam Interview Question for SDE-2s


Country: India
Interview Type: Phone Interview




Comment hidden because of low score. Click to expand.
5
of 5 vote

This is ambiguous:
There are two different IDs for each User record (one for Name Key and one for Age key).

Here is a basic assumption:
- Assuming we want to retrieve both keys (e.g. 1,2 in the prev example)
- Assuming each Name record is followed by Age record
- Assuming table name is Users

If the Ids are sequential with no Gaps. It is quiet easy:
SELECT t1.Id, t2.Id FROM
Users as t1 INNER JOIN Users as t2 ON t1.Id + 1=t2.Id AND t1.Key='name' AND t2.Key='age'
WHERE t1.Value LIKE 'h%' AND t2.Value >= 22 AND t2.Value <=35

If the IDs has gaps, you need to Select RowNumber two and Compare based on RowNumber instead of Id

- Sehs April 30, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
1
of 1 vote

There was also a userid column which has same userId value for the same user. I missed that in the question description.

- hulk May 02, 2014 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 votes

age mentioned in the table is not of type int, if thats the case how can we get the records within the specified age range?

- ironman October 01, 2014 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT Id WHERE name LIKE 'h%' AND age >= 22 AND age <= 35 ?

- jbweimar April 29, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

name is a field in the record key, which has value, which needs to be filtered out,similarly age is a field of key table, this has to be done using an inner join query

- Radhakrishna May 12, 2014 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

Search for text will be expensive. First the age filter can be applied followed by search

SELECT name FROM (
SELECT * FROM Table WHERE age >= 22 AND age <= 35 ) AS T WHERE name LIKE 'h%'

- Siva May 11, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

'age' is not a column name its a value under the column Key .

- vinod June 18, 2014 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

Note: ID should be unique per user to identify both the criteria of name and age columns of same user.

select u1.id from users u1 join users u2
on u1.id = u2.id
where u1.value like 'h%' and u2.value between '22' and '35'

- Anonymous June 08, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Select id from users where name like 'h%' and age between 22 and 35

- Srinivas July 04, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select u1.userid from userdetails u1 inner join userdetails u2
on u1.userid=u2.userid where u1.keys='name' and u1.value like 'h%'
and u2.keys='age' and cast(u2.value as int) between 22 and 25

- mmayur October 03, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select A.value from
(select id,key,lead(value) over() as age from table) A
where A.key ='name'
and a.age >=22 and a.age <=35

- Debi October 17, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Table Name: users
columns : id, key , value

select id
from users
where upper(value) like 'H%'
and key = 'name'
intersect 
select id
from users
where key = 'age'
and value between 22 and 35
;

- Monika June 14, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

table : users
columns: id, key, value

select id 
from users u
where key = 'name'
and value like 'H%'
INTERSECT
select id 
from users u
where key = age
and age between 22 and 35

- monika.m.srivastava June 14, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT x.id FROM x
WHERE x.key='name' AND x.value like 'h%' AND x.id IN
{SELECT x.id FROM x WHERE x.key='age' AND x.value BETWEEN 22 AND 35}

- Red December 16, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select id-1 from cc_keyvalue 
where key ='age' and value between 22 and 35 and 
id IN ((select id+1 from cc_keyvalue
where key||value like 'nameh%'));

- letsnailit January 07, 2017 | Flag Reply


Add a Comment
Name:

Writing Code? Surround your code with {{{ and }}} to preserve whitespace.

Books

is a comprehensive book on getting a job at a top tech company, while focuses on dev interviews and does this for PMs.

Learn More

Videos

CareerCup's interview videos give you a real-life look at technical interviews. In these unscripted videos, watch how other candidates handle tough questions and how the interviewer thinks about their performance.

Learn More

Resume Review

Most engineers make critical mistakes on their resumes -- we can fix your resume with our custom resume review service. And, we use fellow engineers as our resume reviewers, so you can be sure that we "get" what you're saying.

Learn More

Mock Interviews

Our Mock Interviews will be conducted "in character" just like a real interview, and can focus on whatever topics you want. All our interviewers have worked for Microsoft, Google or Amazon, you know you'll get a true-to-life experience.

Learn More