Interview Question

Data Scientist Interview

-

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.

15

Based on "Quick and Dirty"'s assumptions above (e.g. 1 week), here's an example [using Bigquery's SQL syntax] query: select round(100*count(case when b.requestor_id is null then 1 else 0 end)/count(a.requester_id),2) as acceptance_rate from Friend_requests as a left join Request_accepted as b on a.sent_to_id = b.acceptor_id and a.requester_id = b.requestor_id where date(a.time) < date_add(current_date(), "-7", "day")

Anonymous on

2

In both tables, concat the requestor and the recipient IDs then do a left join. Friend_requests[111,aaa,01-01-15;222,aaa,02-01-15] request_accepted[aaa,111,02-01-15] Concat and your left join is searching the second table for 111aaa & 222aaa. It finds the first one and the second one is null. You have a 50% acceptance rate. Regarding the dates, alot can be done with them but they are not strictly part of the question. The only thing that dates mean is that you could have multiple requests before an accept so use distinct.

SAR662 on

1

SELECT (CAST(COUNT(r.acceptor_id) AS FLOAT) / CAST(COUNT(f.requestor_id) AS float)) AS acceptance_rate FROM friend_request f FULL OUTER JOIN request_accepted r ON (f.requestor_id=r.requestor_id AND f.sent_to_id = r.acceptor_id) WHERE f.date > (CURRENT_DATE - INTERVAL '30 day');

Anonymous on

0

^ Left join and not outer join. sorry.

Anonymous on

0

Let's say that you can make multiple friend requests but you can only accept once. Group by requester_id, sent_to_id, take the count of that whole table, then divide by the count of the friend_request table

Anonymous on

0

For above query to get the acpt rate count(r.a_id)/count(*)

Dwipam on

0

what about this? the date filter can be applied in the subqueries too if needed. select (select count(*) from reques_accepted)/(select * from friend_request) * 100 as rate

mm on

0

CORRECTION: what about this? the date filter can be applied in the subqueries too if needed. select (select count(*) from reques_accepted)/(select count(*) from friend_request) * 100 as rate

mm on

0

I would use mysql syntax and pay attention to hints and suggestion from previous answers: 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(acc.time, total.time) 7

Leo on

0

CORRECTION: 0 ▼ I would use mysql syntax and pay attention to hints and suggestion from previous answers: 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(acc.time, total.time) <= 7 AND DATEDIFF(SYSDATE, total.time) > 30

Leo on

0

assume that one person can only send one request to another specific individual SELECT COUNT(*)*1.0 / SUM(accepted) AS acceptancerate FROM (SELECT CASE WHEN R.time IS NULL THEN 0 ELSE 1 END AS accepted FROM Friend_request F LEFT JOIN Request_accepted R ON (F.requestor_id = R.requestor_id AND F.send_to_id = R.acceptor_id)) T1

Anonymous on

0

I would try to join on dates too, since a a user can send, cancel, and resend a request. MySQL: Select Count(ra.acceptor_id)/count(fr.requester_id) as Acceptance_rate From Friend_request fr Left outer join Request_accepted ra on ra.acceptor_id = fr.sent_to_id and ra.requestor_id = fr.requester_id and ra.time > fr.time Where Fr.time < date_sub(current_date(), interval 14 day);

Anonymous on

0

I would do : select count(r.a_id),count(*) from request r RIGHT JOIN (select r_id,s_id,max(time) as time from Friend_requests group by r_id,s_id) f ON f.r_id = r.r_id and r.a_id = f.s_id and r.time < date_add(f.time, INTERVAL 7 day);

Dwipam on

30

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)

Quick and dirty on