sql notes: leetcode#597 friend requests 1: overall acceptance rate

Problem

In social network like Facebook or Twitter, people send friend requests and accept others’ requests as well. Now given two tables as below:

Table: friend_request

Table: request_accepted

Write a query to find the overall acceptance rate of requests rounded to 2 decimals, which is the number of acceptance divide the number of requests.
For the sample data above, your query should return the following result.

Note:

• The accepted requests are not necessarily from the table friend_request. In this case, you just need to simply 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 is no requests at all, you should return 0.00 as the accept_rate.

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

Analysis

To solve this problem, we can divide number of unique acceptance by number of unique requests.

• Get number of unique acceptance:

1

SELECT COUNT(DISTINCT requester_id, accepter_id) FROM request_accepted;

• Similarly, get number of unique requests:

1

SELECT COUNT(DISTINCT sender_id, send_to_id) FROM friend_request;

• Divide number of acceptance by number of requests:

1

SELECT (SELECT COUNT(DISTINCT requester_id, accepter_id) FROM request_accepted)/(SELECT COUNT(DISTINCT sender_id, send_to_id) FROM friend_request) AS accept_rate;

• Round result to 2 decimals:

1

SELECT ROUND((SELECT COUNT(DISTINCT requester_id, accepter_id) FROM request_accepted)/(SELECT COUNT(DISTINCT sender_id, send_to_id) FROM friend_request), 2) AS accept_rate;

• Check if result is null, return 0 if it is null:

1

SELECT IFNULL(ROUND((SELECT COUNT(DISTINCT requester_id, accepter_id) FROM request_accepted)/(SELECT COUNT(DISTINCT sender_id, send_to_id) FROM friend_request), 2), 0) AS accept_rate;

Solution

1

SELECT IFNULL(ROUND((SELECT COUNT(DISTINCT requester_id, accepter_id) FROM request_accepted)/(SELECT COUNT(DISTINCT sender_id, send_to_id) FROM friend_request), 2), 0) AS accept_rate;

Link

597. Friend Request 1: Overall Acceptance Rate
(中文版) SQL 笔记: Leetcode#597 Friend Requests 1: Overall Acceptance Rate