[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
MockInterview Staff answered 4 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


[Content Sponsor #1] Start learning through a free trial on Datacamp!
[Content Sponsor #2] $750 discount on any springboard courses including Data Science Career Track which comes with a Job Guarantee!

Leo Liu answered 3 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


[Content Sponsor #1] Start learning through a free trial on Datacamp!
[Content Sponsor #2] $750 discount on any springboard courses including Data Science Career Track which comes with a Job Guarantee!

banani mohapatra answered 3 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


[Content Sponsor #1] Start learning through a free trial on Datacamp!
[Content Sponsor #2] $750 discount on any springboard courses including Data Science Career Track which comes with a Job Guarantee!

Jessica answered 1 year ago

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


[Content Sponsor #1] Start learning through a free trial on Datacamp!
[Content Sponsor #2] $750 discount on any springboard courses including Data Science Career Track which comes with a Job Guarantee!

Your Answer

0 + 2 =

Data Science Career Bootcamp