- ************************************************************
- *******************************************************************************************
- *****************************************
Table: accepted_request
Log when a friend request is accepted
col_name | col_type ----------------+------------------------ sender_id | bigint recipient_id | bigint acceptance_dt | date
Sample results
id -------------- 1000020
Solution postgres
SELECT user_id_1 AS user_id
FROM (
SELECT sender_id AS user_id_1, recipient_id user_id_2
FROM accepted_request
UNION
SELECT recipient_id AS user_id_1, sender_id user_id_2
FROM accepted_request
) X
GROUP BY 1
ORDER BY COUNT(DISTINCT user_id_2) DESC
LIMIT 1;
Explanation
This query selects a user (aliasing it as "user_id") who has the most distinct accepted requests with other users.
To do this, it starts by creating a subquery that combines the sender and recipient IDs from the "accepted_request" table. It does this twice, once for each direction of the request (sender to recipient and recipient to sender). This is done using the UNION operator.
The resulting table is then grouped by the "user_id_1" column (which contains the user ID from the first column of the subquery). It counts the distinct "user_id_2" values (which contains the user ID from the second column of the subquery) for each "user_id_1". The results are then sorted in descending order based on the count of distinct "user_id_2" values.
Finally, the query selects only the first row (using LIMIT 1), which will be the user with the most distinct accepted requests with other users.
Expected results