logo

Back to questions

Page Recommendation

Hard

Write a query to recommend a page to a user. A recommendation is based on a page liked by user friends. Assume you have two tables: a two-column table of users and their friends, and a two-column table of users and the pages they liked.

Assumptions:

  • Only recommend the top page to the user, and do not recommend pages that were already liked by the user.
  • Top page is defined as the page with the highest number of followers.

Output the user id and page recommended. Order the result in ascending order by user id.

Table:

Column NameType
idinteger
user_idstring
friend_idstring

Example Input:

iduser_idfriend_id
1alicebob
2alicecharles
3alicedavid

Table:

Column NameType
idinteger
user_idstring
page_idstring

Example Input:

iduser_idpage_id
1alicegoogle
2alicefacebook
3bobgoogle
4boblinkedin
5bobfacebook

Example Output:

user_idPage_Recommended
alicelinkedin

Alice's friend Bob is following Google, Linkedin, and Facebook pages. However, since Alice is already following Google and Facebook, the only page that can be recommended to her is Linkedin.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.