Infibeam Interview Question
SDE-2sCountry: India
Interview Type: Phone Interview
There was also a userid column which has same userId value for the same user. I missed that in the question description.
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%'
This is ambiguous:
- Sehs April 30, 2014There 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