sql - Relational Division: Exact Division -

i came across following problem:

write query find users same friends user u.

here tables (and sql fiddle: http://sqlfiddle.com/#!9/457260/1 ):

• users:
  • user_id: int
• friendships:
  • user_id: int
  • friend_id: int

the issue have query returns users have same friends or more user u.

select * users inner join friendships on users.id = friendships.user_id users.id != 1 , friendships.friend_id in (   select distinct friendships.friend_id   friendships   friendships.user_id = 1 ) group users.id having count(distinct friendships.friend_id) = (   select count(distinct friendships.friend_id)   friendships   friendships.user_id = 1 ); 

the simplest way aggregate friends , comparison:

with f (       select user_id, array_agg(friend_id order friend_id) friends       friendships f       group user_id      ) select user_id f f.friends = (select friends f user_id = 1) ,       f.user_id <> 1;