597 Friend Requests I: Overall Acceptance Rate

Description

Table: FriendRequest

• This table may contain duplicates (In other words, there is no primary key for this table in SQL).
• This table contains the ID of the user who sent the request, the ID of the user who received the request, and the date of the request.

Table: RequestAccepted

• This table may contain duplicates (In other words, there is no primary key for this table in SQL).
• This table contains the ID of the user who sent the request, the ID of the user who received the request, and the date when the request was accepted.

Problem Statement

Find the overall acceptance rate of requests, which is the number of acceptance divided by the number of requests. Return the answer rounded to 2 decimals places.

Note:

• The accepted requests are not necessarily from the table friend_request. In this case, Count the total accepted requests (no matter whether they are in the original requests), and divide it by the number of requests to get the acceptance rate.
• It is possible that a sender sends multiple requests to the same receiver, and a request could be accepted more than once. In this case, the ‘duplicated’ requests or acceptances are only counted once.
• If there are no requests at all, you should return 0.00 as the accept_rate.

The result format is in the following example.

Example 1:

Input:

• FriendRequest table:

• RequestAccepted table:

Output:

Explanation: There are 4 unique accepted requests, and there are 5 requests in total. So the rate is 0.80.

Follow up:

• Could you find the acceptance rate for every month?
• Could you find the cumulative acceptance rate for every day?

Solution

The problem can be divided into following parts:

1. Find the total number of unique accepted requests. This table may have duplicates, so to find unique accepted requests, you can use the combination of requester_id and accepter_id columns from the RequestAccepted table.

1SELECT DISTINCT requester_id, accepter_id FROM RequestAccepted

Once you have found all unique accepted requests, you have to count the number of such requests using the COUNT function.

1SELECT COUNT(1) FROM (
2    SELECT DISTINCT requester_id, accepter_id 
3        FROM RequestAccepted
4);

2. Find the total number of requests. In this case, to find the all requests, you can use the FriendRequest table. Again, you can have duplicates, so you first have to find the distinct combination of sender_id and send_to_id columns from the FriendRequest table.

1SELECT DISTINCT sender_id, send_to_id FROM FriendRequest

Once you have the unique friend requests, you have to count the number of such requests using the COUNT function.

1SELECT COUNT(1) FROM (
2    SELECT DISTINCT sender_id, send_to_id 
3        FROM FriendRequest
4);

3. Find the acceptance rate by dividing the number of accepted requests by the total number of requests. You have to use ROUND function to round the result to two decimal places.

1SELECT ROUND(
2    (SELECT COUNT(1) FROM (
3        SELECT DISTINCT requester_id, accepter_id 
4            FROM RequestAccepted
5    ) accepted_requests) /
6    (SELECT COUNT(1) FROM (
7        SELECT DISTINCT sender_id, send_to_id 
8            FROM FriendRequest
9    ) friend_requests), 2) AS accept_rate;

4. One more caveat is that if there are no records in the tables, you have to return 0.00 as the result. If there are no records, you will get NULL value, so you have to use COALESCE or IFNULL function to return 0.00 as the result.

 1SELECT ROUND(
 2    IFNULL(
 3        (SELECT COUNT(1) FROM (
 4            SELECT DISTINCT requester_id, accepter_id 
 5                FROM RequestAccepted
 6        ) accepted_requests) /
 7        (SELECT COUNT(1) FROM (
 8            SELECT DISTINCT sender_id, send_to_id 
 9                FROM FriendRequest
10        ) friend_requests)
11    , 0), 2) AS accept_rate;