Friend Requests I: Overall Acceptance Rate

You can write a query that calculates the overall acceptance rate by dividing the count of unique accepted requests by the count of unique requests. If there are no requests, the query will return 0.00. Here’s the query:

1
2
3
4
5
6
7

SELECT 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 query calculates the count of unique accepted requests using COUNT(DISTINCT requester_id, accepter_id) from the RequestAccepted table.
  • The query calculates the count of unique requests using COUNT(DISTINCT sender_id, send_to_id) from the FriendRequest table.
  • It divides the number of unique accepted requests by the number of unique requests and rounds the result to two decimal places using the ROUND function.
  • If there are no requests, IFNULL returns 0, and the result will be 0.00.