Match requests and accepts into friendships in SQL

Given two event tables, write a single SQL query (PostgreSQL) to output unique undirected friendships with the earliest valid friendship_date. Schema and sample data: Tables: friend_requests(req_id INT, requester_id INT, requestee_id INT, request_date DATE); friend_accepts(accepter_id INT, requester_id INT, accept_date DATE). Sample friend_requests:

+--------+--------------+-------------+-------------+
| req_id | requester_id | requestee_id| request_date|
+--------+--------------+-------------+-------------+
| 1 | 101 | 102 | 2025-08-01 |
| 2 | 102 | 101 | 2025-08-02 |
| 3 | 103 | 104 | 2025-08-05 |
| 4 | 105 | 106 | 2025-08-10 |
| 5 | 105 | 106 | 2025-08-12 |
| 6 | 107 | 108 | 2025-08-15 |
+--------+--------------+-------------+-------------+
Sample friend_accepts:
+-------------+--------------+------------+
| accepter_id | requester_id | accept_date|
+-------------+--------------+------------+
| 102 | 101 | 2025-08-03 |
| 104 | 103 | 2025-08-06 |
| 106 | 105 | 2025-08-11 |
| 108 | 107 | 2025-08-14 | -- invalid (accept before request)
| 106 | 105 | 2025-08-13 |
+-------------+--------------+------------+

Rules: a) A valid acceptance must match a prior request in the opposite direction (accepter_id = requestee_id AND requester_id = requester_id) with accept_date >= request_date. b) Output one row per undirected pair (user_a, user_b) where user_a = LEAST(requester_id, requestee_id) and user_b = GREATEST(requester_id, requestee_id). c) If multiple valid accepts exist for a pair, keep the earliest accept_date. d) Ignore accepts with no prior matching request or that occur before any matching request. Return columns: user_a, user_b, friendship_date. Also explain how your query avoids double-counting when both sides sent requests, and how you would index these tables to make the query fast on 1B-row logs.

Match Requests and Accepts into Unique Friendships

You are given two event tables tracking friend requests and friend acceptances. A friendship is formed when a user accepts a prior request from another user. Your task is to write a single PostgreSQL query that outputs unique undirected friendships with the earliest valid friendship_date.

Table schemas:

• friend_requests(req_id INT, requester_id INT, requestee_id INT, request_date DATE)
• friend_accepts(accepter_id INT, requester_id INT, accept_date DATE)

Rules:

1. A valid acceptance must match a prior request in the opposite direction, meaning:
   • friend_accepts.accepter_id = friend_requests.requestee_id
   • friend_accepts.requester_id = friend_requests.requester_id
   • friend_accepts.accept_date >= friend_requests.request_date
2. Output one row per undirected pair (user_a, user_b), where:
   • user_a = LEAST(requester_id, requestee_id)
   • user_b = GREATEST(requester_id, requestee_id)
3. If multiple valid accept events exist for the same undirected pair, keep only the earliest accept_date as friendship_date.
4. Ignore accept events that have no prior matching request or that occur before any matching request.

Return columns: user_a, user_b, friendship_date.

Also be prepared to explain (outside of SQL) how your query avoids double-counting when both sides send requests and accepts, and what indexes you would add on these tables to make this query efficient on logs with up to 1 billion rows.

Table friend_requests

column	type	constraints
req_id	INT	PRIMARY KEY, NOT NULL
requester_id	INT	NOT NULL
requestee_id	INT	NOT NULL
request_date	DATE	NOT NULL

Sample data:

req_id	request_date	requestee_id	requester_id
1	2025-08-01	102	101
2	2025-08-02	101	102
3	2025-08-05	104	103
4	2025-08-10	106	105
5	2025-08-12	106	105
6	2025-08-15	108	107

Table friend_accepts

column	type	constraints
accepter_id	INT	NOT NULL
requester_id	INT	NOT NULL
accept_date	DATE	NOT NULL

Sample data:

accept_date	accepter_id	requester_id
2025-08-03	102	101
2025-08-06	104	103
2025-08-11	106	105
2025-08-14	108	107
2025-08-13	106	105

Expected output

One row per unique undirected friendship (user_a < user_b) with the earliest valid accept_date as friendship_date, considering only accepts that match a prior request and occur on or after the request_date.

friendship_date	user_a	user_b
2025-08-03	101	102
2025-08-06	103	104
2025-08-11	105	106

Hints

1. First, join friend_requests and friend_accepts only on valid matches (IDs and accept_date >= request_date).
2. Normalize each pair to an undirected edge using LEAST() and GREATEST(), then aggregate to pick the minimum accept_date per pair.