- **************************************************************.
- **************************************************************************************
- **********************************************************************************
Table 1: friends
Users and their friends, each row is a pair of friends.
col_name | col_type -------------+-------------------------- user_id | bigint friend_id | bigint
Table 2: liked_page
Pages a user liked.
col_name | col_type -------------+-------------------------- user_id | bigint page_id | bigint
Sample results
page_id ------------- 2001 2002
Solution postgres
WITH friends_liked_pages AS
(
SELECT user_id, page_id
FROM liked_page
WHERE user_id IN (
SELECT friend_id FROM exercise.friends WHERE user_id = 1
)
),
page_liked_count AS (
SELECT page_id, COUNT(DISTINCT user_id) AS num_liked_friends
FROM friends_liked_pages
WHERE page_id NOT IN (
SELECT page_id FROM liked_page WHERE user_id = 1
)
GROUP BY page_id
)
SELECT
page_id
FROM page_liked_count
ORDER BY num_liked_friends DESC
LIMIT 8;
Explanation
This query is finding the top 8 pages that are liked by the friends of user 1, but are not liked by user 1 themselves.
The query first creates a temporary table called "friends_liked_pages" which contains the user_id and page_id of all the pages that are liked by the friends of user 1.
Then, another temporary table called "page_liked_count" is created which counts the number of unique friends that have liked each page in "friends_liked_pages", excluding user 1 themselves.
Finally, the query selects the page_ids from "page_liked_count" table and orders them by the number of unique friends that have liked each page in descending order, limiting the result to the top 8 pages.
Expected results