Pages liked by people you follow

SQL data engineering interview problem. Difficulty: advanced. Pattern: Self Joins. About 16 minutes. Part of the Pro drill bank.

A social app wants to recommend pages. The follows table says that follower_id follows followee_id, and page_likes lists the pages each user liked. For each user, find the pages that at least one person they follow has liked, but that the user has not liked themselves. Return 1 row for each (user, page) pair. If three people you follow all like the same page, it still appears once. Return user_id and page_id, ordered by page_id and then user_id.

Requirements

• Join each follower to the pages liked by the people they follow.
• Remove pages the follower has already liked.
• Return each (user, page) pair only once.
• Return user_id, page_id, ordered by page_id, then user_id.

Constraints

• A user can follow many people, and a page can be liked by many of them.
• Your result column for the follower must be named user_id.
• Use DISTINCT, or group the rows, so a page liked by several followed people is not repeated.

Examples

Input: follows follower_id | followee_id 1 | 2 4 | 2 4 | 3 5 | 4 2 | 3 6 | 5 7 | 6 page_likes user_id | page_id 2 | P1 3 | P1 5 | P2 6 | P1 Output: user_id | page_id 1 | P1 4 | P1 7 | P1 6 | P2 Why this passes: User 1 follows 2 (P1). User 4 follows 2 and 3 (both P1, 1 row). User 6 follows 5 (P2). User 7 follows 6 (P1). User 2 already likes P1, so nothing is suggested to them.

Input: follows follower_id | followee_id 1 | 2 page_likes user_id | page_id 2 | P1 Output: user_id | page_id 1 | P1 Why this passes: One follow, one liked page.

Topics: lakebench, sql, recommendation, second degree, anti join.

More SQL interview questions · All interview problems · Learn data engineering