Post

Find the number of a user's friends' friend who are also the user's friend. Output the user id along with the count.

The Table Consists of two attributes: user_id and Friend_id

Goal
Find the count of Mutual Friendsâ€™ respective to each user_id.

By in on Jun 01 2024
• Jul, 2024 24

Great post. Learned a lot from this. Keep writing more.

• 0
• Jun, 2024 10

WITH UserFriends AS (    SELECT user_id, friend_id    FROM google_friends_network)SELECT     uf.user_id,    COUNT(DISTINCT gf.friend_id) AS mutual_friend_countFROM  UserFriends ufJOIN     google_friends_network gf ON uf.friend_id = gf.user_idGROUP BY     uf.user_id;

• 0
• Jun, 2024 5

WITH UserFriends AS (    SELECT user_id, friend_id    FROM google_friends_network), FriendsOfFriends AS (    SELECT uf.user_id, gf.friend_id AS friend_of_friend    FROM UserFriends uf    JOIN google_friends_network gf ON uf.friend_id = gf.user_id)SELECT user_id, COUNT(DISTINCT friend_of_friend) AS mutual_friend_countFROM FriendsOfFriendsGROUP BY user_id;

• 0
• Jun, 2024 3

You can use below query in SQL.

WITH UserFriends AS (    SELECT user_id, friend_id    FROM google_friends_network),FriendsOfFriends AS (    SELECT uf.user_id, gf.friend_id AS friend_of_friend    FROM UserFriends uf    JOIN google_friends_network gf ON uf.friend_id = gf.user_id),MutualFriends AS (    SELECT fof.user_id, COUNT(1) AS mutual_friend_count    FROM FriendsOfFriends fof    JOIN google_friends_network uf ON fof.user_id = uf.user_id AND fof.friend_of_friend = uf.friend_id    GROUP BY fof.user_id)SELECT user_id, mutual_friend_countFROM MutualFriends;

• 0