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.

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);