I would like to count the common friends between users. However, I’m struggling in coming with an approach as the users are in the same column.
Imagine the following dummy tables:
with users (user_id, user_name)
as (values
(7,' Adam'),
(5,' Tom'),
(35,' Bob'),
(72,' Charlie'),
(2,' Maria'),
(10,' Isabel')
),
friendships (user_id, friend_id)
as ( values
(7, 101),
(7, 102),
(7, 103),
(7, 104),
(7, 105),
(35, 101),
(35, 102),
(35, 103),
(35, 104),
(35, 105),
(7, 201),
(7, 202),
(7, 203),
(2, 201),
(2, 202),
(2, 203),
(7, 301),
(7, 302),
(72, 301),
(72, 302),
(5, 401),
(5, 402),
(5, 403),
(5, 404),
(5, 405),
(5, 406),
(2, 401),
(2, 402),
(2, 403),
(2, 404),
(2, 405),
(2, 406),
(5, 501),
(5, 502),
(5, 503),
(5, 504),
(10, 501),
(10, 502),
(10, 503),
(10, 504),
(5, 601),
(35, 601),
(35, 602),
(35, 603)
)
So the output would be something like this:
id_1 name_1 id_2 name_2 common_friends_count
7 Adam 35 Bob 5
7 Adam 2 Maria 3
7 Adam 72 Charlie 2
5 Tom 2 Maria 6
5 Tom 10 Isabel 4
5 Tom 35 Bob 1
... etc
Wording the above:
Adam and Bob have 5 friends in common; Adam and Maria have 3 friends in common and so on…
>Solution :
You need to use two different instances of users and friendships. It is rather straightforward but if something is unclear let me know and I’ll add an explanation:
with users ...
)
select f1.user_id id_1, u1.user_name name1
, f2.user_id id_2, u2.user_name name2
, count(1)
from friendships f1
join friendships f2
on f1.friend_id = f2.friend_id
and f1.user_id <> f2.user_id
join users u1
on u1.user_id = f1.user_id
join users u2
on u2.user_id = f2.user_id
group by f1.user_id, u1.user_name, f2.user_id, u2.user_name
order by 1,3