Amazon Interview Question for Software Engineer / Developers


Team: Amazon Instant Video
Country: UK
Interview Type: Phone Interview




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

select g.id from genre g where g.genre='Action'
INTERSECT
select g.id from genre g where g.genre='Comedy'

- San June 20, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
2
of 4 vote

Why are you making it complex...it is just

select g.id from gener g where g.genre in ("Action", "Comedy");

- Anonymous June 21, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 2 votes

I think your query is an OR query, whereas the question asks for an AND.

- Aayush June 21, 2014 | Flag
Comment hidden because of low score. Click to expand.
0
of 2 votes

This is correct query 'select g.id from gener g where g.genre in ("Action", "Comedy");'as there is only 1 column if you put 'and' result will be 0

- Shabi June 24, 2014 | Flag
Comment hidden because of low score. Click to expand.
0
of 2 votes

I think Aayush is correct. Query above will give id,s where either action or comedy (or action&comedy) was present. This query does not answer the question correctly, if you want to use this particular query, I would suggest using something like this

select g.id,count(*) from gener g where g.genre in ("Action", "Comedy") group by g.id having count(*) > 1

- SQLGuru August 17, 2014 | Flag
Comment hidden because of low score. Click to expand.
2
of 2 vote

select id from Genre where Genre ='Action' and id in
(select id from Genre where Genre='Comedy');

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

(Not sure of this:)

select distinct a.id from genre a, genre b
where a.genre = "action" and b.genre = "comedy" and a.id = b.id;

Will be very inefficient, however! The distinct is because it's a bad query, it will come up with a full cross-product.

- JeffD July 06, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
1
of 1 vote

--SELF JOIN---
SELECT G1.ID FROM GENRE G1
JOIN GENRE G2 ON G1.ID = G2.ID
WHERE G1.ID = 'ACTION' AND G2.ID = 'COMEDY';

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

For clarity on why a simple where with or wont work.
20 Action
21 Action
22 Comedy
21 Comedy
Supposed to return 21 as it is both Action and Comedy.

If the nested query in the loop sounds confusing, albeit something longer here
select g.id
from genre g, genre x
where g.id = x.id and
((g.Genre = 'Action' and x.Genre='Comedy') or (g.Genre='Comedy' and x.Genre='Action'))

- subbu August 17, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

Actually any one where should be good enough (Otherwise unnecessary duplicates and distinct).

- subbu August 17, 2014 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

; with ctetst
as
(
select id, 
genre, 
DENSE_RANK() over (partition by id order by genre) as i
from genre)

select id
from ctetst
group by id, i
having i > 1

- Niraj September 05, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT g.Id FROM Genre AS g
WHERE g.Genre = 'Action' OR g.Genre = 'Comedy'
GROUP BY g.Id HAVING COUNT(g.Id) = 2;

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

This question is ambiguous. What is the primary key of the table?

- Christian June 23, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT ID
FROM GENRE
WHERE GENRE IN ('ACTION','COMEDY')
GROUP BY ID
HAVING COUNT(DISTINCT(GENRE)) > 1

- Anonymous July 13, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select id from genre
where genre in ('action', 'comedy');

- Pooja Arondekar September 15, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select id,count(distinct gener)
from xxtest1
where gener in ('Action','Comedy')
group by id
having count(distinct gener) > 1;

- Thavva February 11, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT ID FROM #GENER WHERE GENER IN('ACTION','COMEDY') GROUP BY ID HAVING COUNT(DISTINCT GENER)>1

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

--simple answer
SELECT ID FROM #GENER WHERE GENER IN('ACTION','COMEDY') GROUP BY ID HAVING COUNT(DISTINCT GENER)>1

- Mano June 30, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

I would suggest to use the self join, because intersect doesn't work in mySQL and EXISTS will give you all rows as the output.

SELECT A.id
FROM Genre AS A JOIN Genre AS B ON A.id = B.id
WHERE A.genre = "Action" AND B.genre = "Comedy";

- Juhi January 23, 2018 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select id from Genre where Genre ='Action' and id in
(select id from Genre where Genre='Comedy');

- itsmeashishsingh February 07, 2019 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Ans a)Select distinct Id from Genre
where genre in (select genre from genre where genre = 'Action' and 'Comdey')

Ans b) select distinct Id from Genre
where genre = 'Action' and 'Comedy'

- Anonymous December 27, 2019 | Flag Reply
Comment hidden because of low score. Click to expand.
-1
of 3 vote

Select Id from Genre
where genre = 'action' OR genre = 'comedy'

- 4661 August 03, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
-2
of 2 vote

Taken from w3 schools

Example:

SELECT * FROM Customers
WHERE City='Berlin'
OR City='M√ľnchen';

Solution:

SELECT Id FROM Genre
WHERE Genre='Action'
OR Genre='Comedy';

- LinskeyD July 11, 2014 | 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