Task
At Meta, the Facebook Friends graph team wants to identify users whose first friendship creation happened within 7 days of each other. This can be useful when analyzing early-network formation patterns around surfaces like Facebook Groups and downstream AARRR activation behavior.
Write a SQL query to find pairs of users who became friends within 7 days of each other.
Requirements
- Use each user's earliest friendship timestamp as the date they first became friends on Facebook.
- Return each qualifying pair only once, with
user_id_1 < user_id_2.
- Include the two users' first friendship timestamps and the absolute day difference.
- Exclude users who have no valid friendship timestamp.
- Order the output by
days_apart, then user_id_1, then user_id_2.
Table Definitions
friendships
| column | type | description |
|---|
| friendship_id | INT | Unique friendship event ID |
| user_id_1 | INT | One side of the friendship edge |
| user_id_2 | INT | Other side of the friendship edge |
| became_friends_at | TIMESTAMP | Timestamp when the friendship was created |
| surface | VARCHAR(50) | Meta surface where the connection was initiated |
Sample Data
| friendship_id | user_id_1 | user_id_2 | became_friends_at | surface |
|---|
| 1008 | 108 | 101 | 2024-01-08 11:00:00 | fb_groups |
| 1002 | 102 | 103 | 2024-01-03 09:30:00 | reels |
| 1005 | 105 | 106 | 2024-01-10 18:20:00 | ig_save |
| 1001 | 101 | 102 | 2024-01-01 10:00:00 | fb_groups |
| 1009 | 109 | 110 | NULL | reels |
| 1004 | 104 | 105 | 2024-01-09 15:00:00 | fb_groups |
Expected Output
| user_id_1 | user_id_2 | first_friendship_at_1 | first_friendship_at_2 | days_apart |
|---|
| 101 | 102 | 2024-01-01 10:00:00 | 2024-01-01 10:00:00 | 0 |
| 103 | 104 | 2024-01-03 09:30:00 | 2024-01-03 09:30:00 | 0 |
| 105 | 106 | 2024-01-09 15:00:00 | 2024-01-10 18:20:00 | 1 |
