How to find common matches for different column values in SQL

Question

I have the following table "Friends":

The goal is to find out how many users have the exact same list of friends. In this case, the result would be user_id 1 and user_id 4 since both user 1 and user 4 are friends with "2" and "3".

I think I am on the right track by using the code below:

SELECT * FROM Friends A, Friends B WHERE A.friend_id=B.friend_id AND A.user_id <> B.user_id

However, I am not able to figure out how to finish the query so that it calculates the matching list of friends. Does anyone have any suggestions?

Answer 1

You didn't provide your SQL type.

For MySQL, you can group concat the friends for every user and cross by this value. The default is ",". Then join the same table to compare by the same list of friends.

SELECT t1.friends, t1.friends FROM
(
SELECT user_id,GROUP_CONCAT(friend_id) as friends
FROM friends
GROUP BY friend_id
) as t1
JOIN

(
SELECT user_id,GROUP_CONCAT(friend_id) as friends
FROM friends
GROUP BY friend_id
) as t2
on t1.friends = t2.friends