SQL data engineering interview problem. Difficulty: advanced. Pattern: Self Joins. About 16 minutes. Part of the Pro drill bank.
follows says that follower_id follows followee_id. page_likes lists the pages each user liked. For each user, return the pages liked by at least one person they follow that the user has not liked themselves. Each (user, page) pair appears once, however many followed people liked the page. Columns: user_id, page_id. Order by page_id, then user_id.
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, one 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
Interview-style drill: Recommend pages a user's followees like that the user has not liked.
`follows` says that `follower_id` follows `followee_id`. `page_likes` lists the pages each user liked. For each user, return the pages liked by at least one person they follow that the user has **not** liked themselves. Each (user, page) pair appears once, however many followed people liked the page. Columns: `user_id`, `page_id`. Order by `page_id`, then `user_id`.