CODEWITHSUNDEEP
sqldatabase
user2916886
user2916886

Reputation: 845

SQl query for (person,theatre) relation

I recently appeared for an interview and was asked to write SQL query for this question-

There is table visit(person,theatre) which denotes that a person p visits a theatre t. Now a person p1 is considered to be more movie lover if he visits every theatre that another person p2 visits. I was supposed to write an SQL query to find the most movie loving person (a person who is more movie lover than every other person).

I really had no idea about how to answer it. I mumbled about using sub queries and aggregate operators but couldnt answer it.

Can anyone tell me what the query be?

NOTE: I was told that I cannot use recursion here

Upvotes: 0

Views: 542

Answers (3)

Mohini Mhetre
Mohini Mhetre

Reputation: 919

select person,count(theatre) from Visit Group By person having max(count(theatre)))

Upvotes: 0

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186688

The description is quite vague, so if we state the problem as "is there any person who has visited all the theaters all other persons have" we can put it like that:

select v.person
  from visit v
 where v.theatre = all ( 
         select vt.theatre
           from visit vt)

Upvotes: 1

keltar
keltar

Reputation: 18399

Description is vague, but something like

select person, count(*) as cnt
from visit
group by person
order by cnt desc

Upvotes: 0

Related Questions