You are given the list of LinkedIn connections and the list of company pages that users follow. Your task is to create a new recommendation system for LinkedIn. For each LinkedIn user, find the company pages that this user doesn't follow but at least one of their connection does.
Output the user ID and the ID of the page that should be recommended to this user.
Sample Input:-
Table users_connection:-
user_id: int
connection_id : int
Table users_company_pages:-
user_id: int
page_id: int
Sample Output:-
My Solution:-
SELECT
DISTINCT c.user_id, p.page_id
FROM users_connection c
JOIN users_company_pages p
ON c.connection_id= p.user_id
WHERE (c.connection_id, p.page_id) NOT IN (SELECT user_id, page_id FROM users_company_pages )
Thanks for reading. I hope you enjoyed this fun little case study - it was fun for me to create!. You can also share your approach to solve this problem.
