Friend Requests I: Overall Acceptance Rate
Problem
FriendRequest(sender_id, send_to_id, request_date) and RequestAccepted(requester_id, accepter_id, accept_date). Return overall acceptance rate, rounded to 2 dp.
fr=[(1,2,'a'),(1,3,'b'),(1,4,'c')] ac=[(1,2,'a'),(1,3,'b')]0.67SELECT ROUND(
IFNULL(
(SELECT COUNT(DISTINCT requester_id, accepter_id) FROM RequestAccepted) /
(SELECT COUNT(DISTINCT sender_id, send_to_id) FROM FriendRequest),
0
), 2) AS accept_rate;Explanation
The acceptance rate is simply how many requests were accepted divided by how many were sent. The only tricky bits are avoiding double-counting and avoiding a divide-by-zero error.
A person might send the same friend request more than once, so we use COUNT(DISTINCT sender_id, send_to_id) to count each unique sent pair only once. The same idea with COUNT(DISTINCT requester_id, accepter_id) counts each unique accepted pair once.
Dividing the accepted count by the sent count gives the rate, and ROUND(..., 2) trims it to two decimals.
The IFNULL(..., 0) guards the case where no requests were sent at all: division by zero yields NULL, and we replace that with 0.
Example: 3 unique requests were sent and 2 of those pairs accepted, so the rate is 2 / 3 ≈ 0.67.