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

Table: google_friends_network
The Table Consists of two attributes: user_id and Friend_id

Goal
Find the count of Mutual Friends’ respective to each user_id.

By Akshima Aggarwal in .NET on Jun 01 2024
  • Nikolas
    Jul, 2024 24

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

    • 0
  • Saif Shaikh
    Jun, 2024 10

    1. WITH UserFriends AS (
    2. SELECT user_id, friend_id
    3. FROM google_friends_network
    4. )
    5. SELECT
    6. uf.user_id,
    7. COUNT(DISTINCT gf.friend_id) AS mutual_friend_count
    8. FROM
    9. UserFriends uf
    10. JOIN
    11. google_friends_network gf ON uf.friend_id = gf.user_id
    12. GROUP BY
    13. uf.user_id;

    • 0
  • Tiffany Burris
    Jun, 2024 5

    How about

    1. WITH UserFriends AS (
    2. SELECT user_id, friend_id
    3. FROM google_friends_network
    4. ), FriendsOfFriends AS (
    5. SELECT uf.user_id, gf.friend_id AS friend_of_friend
    6. FROM UserFriends uf
    7. JOIN google_friends_network gf ON uf.friend_id = gf.user_id
    8. )
    9. SELECT user_id, COUNT(DISTINCT friend_of_friend) AS mutual_friend_count
    10. FROM FriendsOfFriends
    11. GROUP BY user_id;

    • 0
  • Jayraj Chhaya
    Jun, 2024 3

    You can use below query in SQL.

    1. WITH UserFriends AS (
    2. SELECT user_id, friend_id
    3. FROM google_friends_network
    4. ),
    5. FriendsOfFriends AS (
    6. SELECT uf.user_id, gf.friend_id AS friend_of_friend
    7. FROM UserFriends uf
    8. JOIN google_friends_network gf ON uf.friend_id = gf.user_id
    9. ),
    10. MutualFriends AS (
    11. SELECT fof.user_id, COUNT(1) AS mutual_friend_count
    12. FROM FriendsOfFriends fof
    13. JOIN google_friends_network uf ON fof.user_id = uf.user_id AND fof.friend_of_friend = uf.friend_id
    14. GROUP BY fof.user_id
    15. )
    16. SELECT user_id, mutual_friend_count
    17. FROM MutualFriends;

    • 0


Most Popular Job Functions


MOST LIKED QUESTIONS