The core idea is simple enough: filter to accepted requests, normalize the id ordering so (1,2) and (2,1) don't show up as separate rows, then deduplicate.
First, filter the friend request logs to only accepted requests. Then, normalize each pair by sorting the two user IDs so the smaller ID comes first, and drop duplicates to ensure each friendship appears once. Finally, select the normalized user columns and the acceptance date.
Pro tip: Clarify the definition of 'accepted date'—it might be a separate column or the date when status changed to accepted. Also, consider edge cases like self-friend requests or duplicate accepted requests.
Select rows where the request status is 'accepted' (or equivalent). This ensures we only consider actual friendships.
Create two new columns: user_min and user_max, where user_min is the smaller of the two user IDs and user_max is the larger. This ensures each pair is represented consistently.
Drop duplicates based on the normalized user pair columns, keeping the first occurrence (or the earliest acceptance date if multiple exist).
Select the normalized user columns and the acceptance date column, and rename them appropriately (e.g., user1, user2, accepted_date).
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.