Friend-Liked Page Suggestions

Medium

A social discovery platform tracks user relationships and page likes in two relational tables. Table: friendship user1_id: one endpoint of a friendship pair user2_id: the other endpoint of the friendship pair each row means both users are direct friends Table: likes user_id: user who liked a page page_id: page identifier each row means the user liked that page Task: Recommend pages for user_id = 1 using pages liked by their direct friends. Recommendation rules: Use only direct friends of user 1. Collect all pages liked by those friends. Exclude pages already liked by user 1. Return each page exactly once. Output requirements: Return one column named recommended_page. Result may be returned in any order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

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

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

Output

[
  {"recommended_page":23},
  {"recommended_page":24},
  {"recommended_page":33},
  {"recommended_page":77}
]

Explanation

Direct friends of user 1 are users 2, 3, and 4. Their liked pages are {23, 24, 33, 77}. Page 88 is excluded because user 1 already liked it.

Example 2

Input

friendship:
| user1_id | user2_id |
|----------|----------|
| 1        | 10       |

likes:
| user_id | page_id |
|---------|---------|
| 1       | 5       |
| 10      | 5       |

Output

[]

Explanation

Friend 10 liked only page 5, but user 1 already likes page 5, so there is no recommendation.

Example 3

Input

friendship:
| user1_id | user2_id |
|----------|----------|
| 2        | 1        |
| 3        | 1        |
| 6        | 7        |

likes:
| user_id | page_id |
|---------|---------|
| 2       | 900     |
| 3       | 901     |
| 6       | 902     |
| 1       | 999     |

Output

[
  {"recommended_page":900},
  {"recommended_page":901}
]

Explanation

Only direct friends of user 1 matter. User 6 is not directly connected to user 1, so page 902 is ignored.

Constraints

0 <= number of rows in friendship <= 200000
0 <= number of rows in likes <= 200000
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
Friendship is an undirected relationship represented by one row per pair
Recommend pages for user_id = 1 only
Exclude pages already liked by user 1
Return each recommended page exactly once
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with one column: recommended_page

Preparing your editor. You can read the problem while it loads.

Friend-Liked Page Suggestions

Medium

A social discovery platform tracks user relationships and page likes in two relational tables. Table: friendship user1_id: one endpoint of a friendship pair user2_id: the other endpoint of the friendship pair each row means both users are direct friends Table: likes user_id: user who liked a page page_id: page identifier each row means the user liked that page Task: Recommend pages for user_id = 1 using pages liked by their direct friends. Recommendation rules: Use only direct friends of user 1. Collect all pages liked by those friends. Exclude pages already liked by user 1. Return each page exactly once. Output requirements: Return one column named recommended_page. Result may be returned in any order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

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

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

Output

[
  {"recommended_page":23},
  {"recommended_page":24},
  {"recommended_page":33},
  {"recommended_page":77}
]

Explanation

Direct friends of user 1 are users 2, 3, and 4. Their liked pages are {23, 24, 33, 77}. Page 88 is excluded because user 1 already liked it.

Example 2

Input

friendship:
| user1_id | user2_id |
|----------|----------|
| 1        | 10       |

likes:
| user_id | page_id |
|---------|---------|
| 1       | 5       |
| 10      | 5       |

Output

[]

Explanation

Friend 10 liked only page 5, but user 1 already likes page 5, so there is no recommendation.

Example 3

Input

friendship:
| user1_id | user2_id |
|----------|----------|
| 2        | 1        |
| 3        | 1        |
| 6        | 7        |

likes:
| user_id | page_id |
|---------|---------|
| 2       | 900     |
| 3       | 901     |
| 6       | 902     |
| 1       | 999     |

Output

[
  {"recommended_page":900},
  {"recommended_page":901}
]

Explanation

Only direct friends of user 1 matter. User 6 is not directly connected to user 1, so page 902 is ignored.

Constraints

0 <= number of rows in friendship <= 200000
0 <= number of rows in likes <= 200000
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
Friendship is an undirected relationship represented by one row per pair
Recommend pages for user_id = 1 only
Exclude pages already liked by user 1
Return each recommended page exactly once
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with one column: recommended_page