Acceptance Rate

friend_requests table

column type
requester_id integer
requested_id integer
created_at datetime

friend_accepts table

column type
acceptor_id integer
requester_id integer
created_at datetime

You’re given two tables. friend_requests holds all the friend requests made and friend_accepts is all of acceptances.

Write a query to find the overall acceptance rate of friend requests.

Write a query to find the overall acceptance rate of friend requests.

Solutions

SELECT 
	ROUND(IFNULL(
    (SELECT COUNT(DISTINCT acceptor_id, requester_id) FROM friend_accepts)
    /
    (SELECT COUNT(DISTINCT requester_id, requested_id) FROM friend_requests),
   0),2) accept_rate

WITH base AS (
  SELECT
    l1.requester_id 
    , l1.requested_id
    , CASE WHEN l2.requester_id IS NOT NULL THEN 1 ELSE 0 END accepted
  FROM friend_requests l1
  LEFT JOIN friend_accepts l2 ON l2.acceptor_id = l1.requested_id
)
SELECT 
	SUM(CASE WHEN accepted = 1 THEN 1 END) accepts
	, COUNT(*) requests
	, accepts/requests acceptance_rate
FROM base