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