PostgreSQL: Count common values of column B between ids of column A

Question

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...

Answer 1

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

Fiddle