Julian J. Tejera Julian J. Tejera - 2 months ago 17
SQL Question

Relational Division: Exact Division

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

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

with f as (
      select user_id, string_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);
Comments