Social Page Recommendations with Friend Signals

Medium

A social platform stores friendship relationships and page-like events in relational tables. Table: friendship user1_id: one endpoint of a friendship pair user2_id: the other endpoint of the pair each row represents a direct friendship link between two users Table: likes user_id: user who liked a page page_id: page identifier each row represents one like action Task: For every user that appears in the friendship graph, generate page recommendations from direct-friend behavior. A page p is recommended to user u when: at least one direct friend of u liked p user u did not like p For each recommendation, also return how many of u's direct friends liked that page. Output requirements: Return columns: user_id, page_id, friends_likes friends_likes is the count of direct friends who liked page_id Return each (user_id, page_id) at most once Output order may be arbitrary Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

friendship:
| user1_id | user2_id |
|----------|----------|
| 1        | 2        |
| 1        | 3        |
| 1        | 4        |
| 2        | 3        |
| 2        | 5        |
| 6        | 1        |

likes:
| user_id | page_id |
|---------|---------|
| 1       | 88      |
| 2       | 23      |
| 3       | 24      |
| 4       | 56      |
| 5       | 11      |
| 6       | 33      |
| 2       | 77      |
| 3       | 77      |
| 6       | 88      |

Output

[
  {"user_id":1,"page_id":23,"friends_likes":1},
  {"user_id":1,"page_id":24,"friends_likes":1},
  {"user_id":1,"page_id":33,"friends_likes":1},
  {"user_id":1,"page_id":56,"friends_likes":1},
  {"user_id":1,"page_id":77,"friends_likes":2},
  {"user_id":2,"page_id":24,"friends_likes":1},
  {"user_id":2,"page_id":56,"friends_likes":1},
  {"user_id":2,"page_id":88,"friends_likes":1},
  {"user_id":3,"page_id":23,"friends_likes":1},
  {"user_id":3,"page_id":88,"friends_likes":1},
  {"user_id":4,"page_id":23,"friends_likes":1},
  {"user_id":4,"page_id":24,"friends_likes":1},
  {"user_id":4,"page_id":77,"friends_likes":1},
  {"user_id":4,"page_id":88,"friends_likes":1},
  {"user_id":5,"page_id":23,"friends_likes":1},
  {"user_id":5,"page_id":77,"friends_likes":1}
]

Explanation

For each user, gather pages liked by direct friends, remove pages already liked by that user, and count how many friends support each remaining page.

Example 2

Input

friendship:
| user1_id | user2_id |
|----------|----------|
| 10       | 11       |
| 10       | 12       |
| 11       | 12       |
| 12       | 13       |

likes:
| user_id | page_id |
|---------|---------|
| 10      | 1       |
| 11      | 2       |
| 11      | 3       |
| 12      | 1       |
| 12      | 2       |
| 12      | 4       |
| 13      | 4       |

Output

[
  {"user_id":10,"page_id":2,"friends_likes":2},
  {"user_id":10,"page_id":3,"friends_likes":1},
  {"user_id":10,"page_id":4,"friends_likes":1},
  {"user_id":11,"page_id":1,"friends_likes":2},
  {"user_id":11,"page_id":4,"friends_likes":1},
  {"user_id":12,"page_id":3,"friends_likes":1},
  {"user_id":13,"page_id":1,"friends_likes":1},
  {"user_id":13,"page_id":2,"friends_likes":1}
]

Explanation

A recommendation score increases when multiple direct friends like the same page. Pages already liked by the target user are excluded even if many friends liked them.

Example 3

Input

friendship:
| user1_id | user2_id |
|----------|----------|
| 21       | 22       |
| 22       | 23       |

likes:
| user_id | page_id |
|---------|---------|
| 21      | 5       |
| 22      | 5       |
| 23      | 5       |

Output

[]

Explanation

All candidate pages are already liked by the target users, so no recommendation rows remain.

Constraints

0 <= number of rows in friendship <= 250000
0 <= number of rows in likes <= 250000
1 <= user1_id, user2_id, user_id, page_id <= 1000000000
(user1_id, user2_id) is unique in friendship
(user_id, page_id) is unique in likes
Each friendship row connects two distinct users
Treat friendship as an undirected relationship
Recommendations are generated for users appearing in friendship
Exclude pages already liked by the target user
friends_likes counts direct friends who liked the recommended page
Return unique (user_id, page_id) rows with friends_likes
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns user_id, page_id, friends_likes

friendship: | user1_id | user2_id | |----------|----------| | 1 | 2 | | 1 | 3 | | 1 | 4 | | 2 | 3 | | 2 | 5 | | 6 | 1 | likes: | user_id | page_id | |---------|---------| | 1 | 88 | | 2 | 23 | | 3 | 24 | | 4 | 56 | | 5 | 11 | | 6 | 33 | | 2 | 77 | | 3 | 77 | | 6 | 88 |

[ {"user_id":1,"page_id":23,"friends_likes":1}, {"user_id":1,"page_id":24,"friends_likes":1}, {"user_id":1,"page_id":33,"friends_likes":1}, {"user_id":1,"page_id":56,"friends_likes":1}, {"user_id":1,"page_id":77,"friends_likes":2}, {"user_id":2,"page_id":24,"friends_likes":1}, {"user_id":2,"page_id":56,"friends_likes":1}, {"user_id":2,"page_id":88,"friends_likes":1}, {"user_id":3,"page_id":23,"friends_likes":1}, {"user_id":3,"page_id":88,"friends_likes":1}, {"user_id":4,"page_id":23,"friends_likes":1}, {"user_id":4,"page_id":24,"friends_likes":1}, {"user_id":4,"page_id":77,"friends_likes":1}, {"user_id":4,"page_id":88,"friends_likes":1}, {"user_id":5,"page_id":23,"friends_likes":1}, {"user_id":5,"page_id":77,"friends_likes":1} ]

friendship: | user1_id | user2_id | |----------|----------| | 10 | 11 | | 10 | 12 | | 11 | 12 | | 12 | 13 | likes: | user_id | page_id | |---------|---------| | 10 | 1 | | 11 | 2 | | 11 | 3 | | 12 | 1 | | 12 | 2 | | 12 | 4 | | 13 | 4 |

[ {"user_id":10,"page_id":2,"friends_likes":2}, {"user_id":10,"page_id":3,"friends_likes":1}, {"user_id":10,"page_id":4,"friends_likes":1}, {"user_id":11,"page_id":1,"friends_likes":2}, {"user_id":11,"page_id":4,"friends_likes":1}, {"user_id":12,"page_id":3,"friends_likes":1}, {"user_id":13,"page_id":1,"friends_likes":1}, {"user_id":13,"page_id":2,"friends_likes":1} ]

friendship: | user1_id | user2_id | |----------|----------| | 21 | 22 | | 22 | 23 | likes: | user_id | page_id | |---------|---------| | 21 | 5 | | 22 | 5 | | 23 | 5 |