# [SQL] Given two tables Friend_request (requester_id, sent_to_id , time) Request_accepted (acceptor_id, requestor_id, time) Find the overall acceptance rate of requests.

Data Science Interview QuestionsCategory: Data Science[SQL] Given two tables Friend_request (requester_id, sent_to_id , time) Request_accepted (acceptor_id, requestor_id, time) Find the overall acceptance rate of requests.
4 Answers

1. Define how long you have to wait before a friend request is considered rejected (e.g. 1 week)
2. Exclude the most recent data, up to the length in 1
3. Compute answer as count(Request_accepted)/count(Friend_request)
Source

SELECT round( 100 * count( DISTINCT total.requestor_id) / count(acc.requestor_is), 2) AS acceptance_rate
FROM friend_request total LEFT JOIN request_accepted acc
ON (total.requestor_id = acc.requestor_id AND total. send_to_id = acc.acceptor_id)
WHERE DATEDIFF(IFNULL(acc.time, total_time) , total.time) <= 7
AND DATEDIFF(SYSDATE, total.time) > 30

The question becomes slightly difficult when the 2 separate tables are combined in just one table with below columns and you have to find monthly acceptance rate. The query becomes an extension of self join which is the most common prototype of questions asked in interviews.
actor_uid|target_uid|action|time
11|22|”sent_request”|5 jan’18
22|11|”accept_request”|15 jan’18
solution:-
select count(case when accept_id is not null end)/count(*) from
(
(select a.send_id,b.accept_id from
(select a.actor_uid as send_id from friending where action =’send_request’ where ds=’2017-05-14′)a
left join
(select a.target_uid as accept_id from friending where action =’accept_request’)b
on   a.actor_uid=b.target_uid
and  a.target_uid=b.actor_uid
)a

(COUNT(*)FROM friend_request JOIN request_accepted ON friend_request.sent_to_id = request_accepted.acceptor_id)/(COUNT (*) FROM friend_request)

Your Answer

6 + 6 =