John's New Best Friend

Using the provided database tables, determine a potential friend for John based on a point-based system. The recommendation system is compliant of the following weights:

• Mutual Friends: +3 points for each mutual friend.
• Page Likes: +2 points for every shared page like.
• Blocked Users: Disqualified if John has blocked them before.
• Already Friends: Disqualified if they are already John’s friends.

You have been given the following tables in the database:

users table:

This table holds information about various users, with user_id being a unique identifier for each user and name being the user’s name.

friends table:

This table lists all the friendships, with user_id and friend_id representing the two users who are friends.

likes table:

This table records which pages each user has liked, with user_id and page_id representing the user and the page respectively.

blocks table:

This table records which users each user has blocked, with user_id and blocked_id representing the user and the blocked user respectively.

Output:

Provide the result in the following format:

This table should list the top user who have the highest “friendship points” according to John’s criteria, along with their total “friendship points”.

Note: John’s user_id is 3

Example:

Input:

users table:

friends table:

likes table:

blocks table:

Points Calculation (without already being John’s friend or blocked):

1. Alice (user 1):

   • Mutual Friends with John: None
   • Mutual Likes with John: 1 (page 10) = 2 points
   • Total points for Alice = 2 points

2. Bob (user 2):

   • Mutual Friends with John: None
   • Mutual Likes with John: None
   • Total points for Bob = 0 points

3. Dave (user 4):

   • He is blocked by John, so he is disqualified and receives 0 points.

Based on the recalculated points, the output table would look like:

Output:

From the output, Alice, with 2 points, is the most suitable potential friend for John based on the provided criteria.