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 –
- 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
The query result format is in the following example.
- 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
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.