Relational Division: Exact Division

Question

I came across the following problem:

Write a query to find any users with exactly the same friends as another user U.

Here are the tables (and a SQL Fiddle: http://sqlfiddle.com/#!9/457260/1 ):

• Users:
  • user_id: Int
• Friendships:
  • user_id: Int
  • friend_id: Int

The issue that I have with my query is that it returns users that have the same friends or more than user U.

SELECT *
FROM users INNER JOIN friendships ON users.id = friendships.user_id
WHERE users.id != 1 AND friendships.friend_id IN (
  SELECT DISTINCT friendships.friend_id
  FROM friendships
  WHERE friendships.user_id = 1
)
GROUP BY users.id
HAVING COUNT(DISTINCT friendships.friend_id) = (
  SELECT COUNT(DISTINCT friendships.friend_id)
  FROM friendships
  WHERE friendships.user_id = 1
);

Answer 1

The simplest way is to aggregate the friends and then do the comparison:

with f as (
      select user_id, array_agg(friend_id order by friend_id) as friends
      from friendships f
      group by user_id
     )
select user_id
from f
where f.friends = (select friends from f where user_id = 1) and
      f.user_id <> 1;