[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.
6 Answers
MockInterview Staff answered 7 years ago

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

Leo Liu answered 7 years ago

 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

banani mohapatra answered 6 years ago

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

Jessica answered 5 years ago

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

Pradnya Raibagkar answered 1 year ago

  select count(acceptor_id)/ count(requester_id) as acceptance_rate_of_requests from Friend_Request FR left join Request_Accepted RA on FR.sent_to_id = RA.acceptor_id where RA.acceptor_id is not null

Pradnya Raibagkar answered 1 year ago

select count(acceptor_id)/ count(requester_id) as acceptance_rate_of_requests
from Friend_Request FR
left join Request_Accepted RA
on FR.sent_to_id = RA.acceptor_id
where RA.acceptor_id is not null

Your Answer

16 + 16 =