Back to questions
Event Friends Recommendation Facebook SQL Interview Question
Event Friends Recommendation
Facebook SQL Interview Question
Facebook wants to recommend new friends to people who show interest in attending 2 or more of the same private events.
Sort your results in order of user_a_id and user_b_id (refer to the Example Output below).
Notes:
- A user interested in attending would have either 'going' or 'maybe' as their attendance status.
- Friend recommendations are unidirectional, meaning if user x and user y should be recommended to each other, the result table should have both user x recommended to user y and user y recommended to user x.
- The result should not contain duplicates (i.e., user y should not be recommended to user x multiple times).
Table:
| Column Name | Type |
|---|---|
| user_a_id | integer |
| user_b_id | integer |
| status | enum ('friends', 'not_friends') |
Each row of this table indicates the status of the friendship between user_a_id and user_b_id.
Example Input:
| user_a_id | user_b_id | status |
|---|---|---|
| 111 | 333 | not_friends |
| 222 | 333 | not_friends |
| 333 | 222 | not_friends |
| 222 | 111 | friends |
| 111 | 222 | friends |
| 333 | 111 | not_friends |
Table:
| Column Name | Type |
|---|---|
| user_id | integer |
| event_id | integer |
| event_type | enum ('public', 'private') |
| attendance_status | enum ('going', 'maybe', 'not_going') |
| event_date | date |
Example Input:
| user_id | event_id | event_type | attendance_status | event_date |
|---|---|---|---|---|
| 111 | 567 | public | going | 07/12/2022 |
| 222 | 789 | private | going | 07/15/2022 |
| 333 | 789 | private | maybe | 07/15/2022 |
| 111 | 234 | private | not_going | 07/18/2022 |
| 222 | 234 | private | going | 07/18/2022 |
| 333 | 234 | private | going | 07/18/2022 |
Example Output:
| user_a_id | user_b_id |
|---|---|
| 222 | 333 |
| 333 | 222 |
Users 222 and 333 who are not friends have shown interest in attending 2 or more of the same private events.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Thanks for all your feedback! We recognised the gaps and have revised our solution based on Konstantin Kuzmin's solution.
Goal: Find pairs of friends to be recommended to each other if they're interested in attending 2 or more of the same private events.
- Find users who are interested in attending private events.
- Join tables to compare the correct data
- Find pairs of users who are not friends but are interested in 2 or more of the same private events.
Step 1: Find users interested in attending private events
We can accomplish this by applying the appropriate filters:
Output for user IDs 222 and 333:
| user_id | event_id |
|---|---|
| 222 | 234 |
| 222 | 789 |
| 333 | 234 |
| 333 | 789 |
Step 2: Join tables to compare the correct data
Now, we can convert the above query into a CTE called and join it to the table. Later, we'll use these tables to search for the pair of users who are not friends but are attending the same events.
Output for user IDs 222 and 333:
| user_a_id | user_b_id |
|---|---|
| 222 | 444 |
| 222 | 444 |
| 222 | 333 |
| 222 | 333 |
| 333 | 222 |
| 333 | 222 |
| 333 | 444 |
| 333 | 444 |
We know the joins can be pretty confusing, so let's break them down here.
Join #1
- We performed a SELF JOIN of the and aliased each one as and .
- In #1, we want to find pairs of different users to recommend to each other. We don't want the same users compared across tables, so we use the operator to make sure the names being pulled from each table are different. Meanwhile, for the events, we use the equal sign to compare users who are interested in the same events.
- Essentially, we want to compare different users who show interest in the same events
- For #2, we JOIN on the usernames in order to check the friendship status for each user pair
Join #2
We joined the table with the table which has their friendship status ('friends' or 'not_friends') based on the common field, user_id with the and fields.
Step 3: Find pairs of users who are not friends but are interested in 2 or more of the same private events.
Keep pushing through– we're almost at the end!
We can accomplish this by:
- Filtering for 'not_friends', and
- Counting the pairs that have 2 or more events (in this case, 2 or more rows of the pairs) in common
Solution: