Julian J. Tejera Julian J. Tejera - 1 year ago 133
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.

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 Source

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);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download