Find the number of a user's friends' friend who are also the user's friend. Output the user id along with the count.
Akshima Aggarwal
Select an image from your device to upload
Table: google_friends_networkThe Table Consists of two attributes: user_id and Friend_id
GoalFind the count of Mutual Friends’ respective to each user_id.
SELECT g1.user_id,COUNT(DISTINCT g2.friend_id) AS mutual_friend_count FROM google_friends_network g1 JOIN google_friends_network g2ON g1.friend_id = g2.user_id JOIN google_friends_network g3ON g1.user_id = g3.user_idAND g2.friend_id = g3.friend_id WHERE g2.friend_id <> g1.user_id GROUP BY g1.user_id ORDER BY g1.user_id;
g1.friend_id = g2.user_id finds each user’s friends’ friends.
g1.friend_id = g2.user_id
g2.friend_id = g3.friend_id checks whether that friend-of-friend is also already a direct friend of the same user.
g2.friend_id = g3.friend_id
COUNT(DISTINCT ...) avoids double counting.simulation games
COUNT(DISTINCT ...)
g2.friend_id <> g1.user_id excludes the user themself.
g2.friend_id <> g1.user_id
If the table stores each friendship only once and not bidirectionally, this query assumes the direction in the table already reflects the friend relation you want.
Great post. Learned a lot from this. Keep writing more.
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;
WITH UserFriends AS (
SELECT user_id, friend_id
FROM google_friends_network
)
SELECT
uf.user_id,
COUNT(DISTINCT gf.friend_id) AS mutual_friend_count
FROM
UserFriends uf
JOIN
google_friends_network gf ON uf.friend_id = gf.user_id
GROUP BY
uf.user_id;
How about
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;
), 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_count
FROM FriendsOfFriends
GROUP BY user_id;
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;
),
FriendsOfFriends AS (
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_count
FROM MutualFriends;