Coding questions · Roblox
Generate Friendship List with Acceptance Dates Using Pandas
This question evaluates proficiency in data manipulation and relational reasoning using Python/pandas, focusing on transforming event logs into canonical pairwise relationships and extracting activation timestamps.
friend_events
| requester_id | accepter_id | request_date | accept_date |
|---|---|---|---|
| 1 | 2 | 2024-01-01 | 2024-01-02 |
| 2 | 3 | 2024-01-03 | 2024-01-05 |
| 3 | 2 | 2024-01-04 | 2024-01-05 |
| 2 | 1 | 2024-01-06 | 2024-01-07 |
| 4 | 1 | 2024-01-08 | 2024-01-09 |
Scenario
After launching a social feature, product wants a list of confirmed friendships with the date they formed.
Question
Given friend request logs, write Python/pandas that returns each distinct user pair once (smaller id first) and the acceptance date when the friendship became active.
Hints
Filter accepted rows, sort ids, drop_duplicates.
Friendship pairs and start date
You are given a log of friend requests and their acceptance dates. Each row represents a request from one user to another, along with the date it was requested and the date it was accepted. A confirmed friendship exists when there is a non-NULL accept_date. Write an SQL query that returns each distinct friendship pair exactly once (with the smaller user id as user1_id and the larger as user2_id) and the date when the friendship became active, defined as the earliest accept_date across all requests in either direction between the two users.
Table friend_events
| column | type | constraints |
|---|---|---|
| requester_id | INTEGER | |
| accepter_id | INTEGER | |
| request_date | DATE | |
| accept_date | DATE |
Sample data:
| accept_date | accepter_id | request_date | requester_id |
|---|---|---|---|
| 2024-01-02 | 2 | 2024-01-01 | 1 |
| 2024-01-05 | 3 | 2024-01-03 | 2 |
| 2024-01-05 | 2 | 2024-01-04 | 3 |
| 2024-01-07 | 1 | 2024-01-06 | 2 |
| 2024-01-09 | 1 | 2024-01-08 | 4 |
Expected output
One row per unique friendship pair (user1_id = smaller id, user2_id = larger id) with the earliest acceptance date when the friendship formed.
| friends_since | user1_id | user2_id |
|---|---|---|
| 2024-01-02 | 1 | 2 |
| 2024-01-09 | 1 | 4 |
| 2024-01-05 | 2 | 3 |
Hints
- Filter to accepted requests where accept_date IS NOT NULL.
- Canonicalize each pair so the smaller user id is always first.
- Group by the canonical pair and take the MIN(accept_date) as the friendship start date.