Question: Friend Recommendation System
"Given a user-friendship table, suggest friends by finding users who share at least 2 mutual friends but are not already directly connected."

Step-by-Step Explanation:

You are given a table called friendships with:

• user_id
• friend_id

This means that user_id and friend_id are friends.

You need to:

1. Find pairs of users who are not direct friends.
2. Suggest users who share at least 2 mutual friends.

Sample Data (friendships):

Step 1: Find Mutual Friends

A mutual friend means:

• User A and User B are both friends with User C.

Example:

• User 1 and User 2:
  • Mutual friend = 3 (both 1 and 2 are friends with 3)

Similarly:

• User 2 and User 3:
  • Mutual friend = 1
• User 2 and User 4:
  • Mutual friend = 3

Step 2: Avoid Suggesting Direct Friends

We must not suggest two users who are already direct friends (already connected in the table).

Step 3: Only Suggest if There Are at Least 2 Mutual Friends

We only suggest user pairs if they have 2 or more mutual friends.

SQL Query:

WITH mutual_friends AS (
  SELECT
    a.user_id AS user_a,
    b.user_id AS user_b,
    a.friend_id AS mutual_friend
  FROM
    friendships a
  JOIN
    friendships b
    ON a.friend_id = b.friend_id
  WHERE
    a.user_id < b.user_id
)

, mutual_friend_counts AS (
  SELECT
    user_a,
    user_b,
    COUNT(*) AS mutual_friend_count
  FROM
    mutual_friends
  GROUP BY
    user_a, user_b
)

, existing_friends AS (
  SELECT 
    user_id, 
    friend_id
  FROM 
    friendships
)

SELECT
  mfc.user_a,
  mfc.user_b,
  mfc.mutual_friend_count
FROM
  mutual_friend_counts mfc
LEFT JOIN
  existing_friends ef
  ON (mfc.user_a = ef.user_id AND mfc.user_b = ef.friend_id)
  OR (mfc.user_a = ef.friend_id AND mfc.user_b = ef.user_id)
WHERE
  ef.user_id IS NULL
  AND mfc.mutual_friend_count >= 2;

Explanation of SQL:

• Step 1: mutual_friends CTE:
  • Find all user pairs who share the same mutual friend.
  • Ensure a.user_id < b.user_id to avoid duplicate pairs (like (1,2) and (2,1)).
• Step 2: mutual_friend_counts CTE:
  • Count how many mutual friends each user pair shares.
• Step 3: existing_friends CTE:
  • List direct friendships (user and their friend).
• Final Step:
  • Left join to check if user_a and user_b are already direct friends.
  • Only suggest users with at least 2 mutual friends and no direct friendship.

Expected Output:

(Example: User 2 and User 3 have at least 2 mutual friends and are not direct friends.)

Important Points:

• Always remove direct friends from suggestions.
• a.user_id < b.user_id avoids duplicate pairs.
• COUNT(*) >= 2 ensures there are at least two mutual friends.