Meta Interview Question
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.
Interview Answers
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")
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.
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');
^ Left join and not outer join. sorry.
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
For above query to get the acpt rate
count(r.a_id)/count(*)
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
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
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
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
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
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);
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);
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)