Facebook SQL Interview Questions – Leetcode 597 – Overall Acceptance Rate

Problem Description –

Write an SQL query to 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 That –

1. 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.
2. 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.
3. If there are no requests at all, you should return 0.00 as the accept_rate.

The query result format is in the following example.

Follow up:

• Could you write a query to return the acceptance rate for every month?
• Could you write a query to return the cumulative acceptance rate for every day?

Difficulty Level – Easy

Problem Link – https://leetcode.com/problems/friend-requests-i-overall-acceptance-rate/

Solution –

SELECT 
	ROUND(
		IFNULL(
	    (SELECT COUNT(*) FROM (SELECT DISTINCT requester_id, accepter_id FROM RequestAccepted) As X) 
	    / 
	    (SELECT COUNT(*) FROM (SELECT DISTINCT sender_id, send_to_id FROM FriendRequest) AS y) 
	    , 0)
    , 2)
    As accept_rate

If you look at the RequestAccepted table below –

You can see that the requester_id 3 has sent two request to the same person with the accepter_id 4 and he/she accepted both the requests. And in the problem description it is given that It could happen and in this case the ‘duplicated’ requests or acceptances are only counted once.

Now one strategy you can take is use COUNT(DISTINCT accepter_id) to count the unique acceptance but there is a problem. If you do this then the count will be 3 but for the correct result it should be 4. So to find the correct counting , you have to some how find the unique count of requester_id and accepter_id together.

To do that first we can use this query –

SELECT DISTINCT requester_id, accepter_id FROM RequestAccepted

This is the exactly result that we wanted. This query get rid of the duplicate request accepted row from the table and only gives us the unique requester_id and accepter_id. Now we can count how many rows are there using a subquery.

SELECT COUNT(*) FROM (SELECT DISTINCT requester_id, accepter_id FROM RequestAccepted) As X

This will now give us the count of 4 that we needed for the correct result.

Now, We can do the same for the number of friend request sent calculation .

Ans we also used IFNULL to replace null values with 0 and round the number to 2 decimal places.

If you like this post then please share it with others and subscribe to our blog for more SQL interview questions.