597. Friend Requests I: Overall Acceptance Rate

In social network like Facebook or Twitter, people send friend requests and accept others’ requests as well. Now given two tables as below:

Table:

friend_request

| sender_id | send_to_id |request_date|
|-----------|------------|------------|
| 1         | 2          | 2016_06-01 |
| 1         | 3          | 2016_06-01 |
| 1         | 4          | 2016_06-01 |
| 2         | 3          | 2016_06-02 |
| 3         | 4          | 2016-06-09 |

Table:

request_accepted

| requester_id | accepter_id |accept_date |
|--------------|-------------|------------|
| 1            | 2           | 2016_06-03 |
| 1            | 3           | 2016-06-08 |
| 2            | 3           | 2016-06-08 |
| 3            | 4           | 2016-06-09 |
| 3            | 4           | 2016-06-10 |

Write a query to find the overall acceptance rate of requests rounded to 2 decimals, which is the number of acceptance divide the number of requests.

For the sample data above, your query should return the following result.

|accept_rate|
|-----------|
|       0.80|

Note:

The accepted requests are not necessarily from the table friend_request. In this case, you just need to simply count the total accepted requests (no matter whether they are in the original requests), and divide it by the number of requests to get the acceptance rate.

It is possible that a sender sends multiple requests to the same receiver, and a request could be accepted more than once. In this case, the ‘duplicated’ requests or acceptances are only counted once.

If there is no requests at all, you should return 0.00 as the accept_rate.

Explanation:

There are 4 unique accepted requests, and there are 5 requests in total. So the rate is 0.80.

Follow-up:

Can you write a query to return the accept rate but for every month?

How about the cumulative accept rate for every day?

# Write your MySQL query statement below
SELECT ifnull(Round(count(distinct requester_id, accepter_id) / count(distinct sender_id, send_to_id), 2),0) as accept_rate
FROM request_accepted, friend_request

Follow up: https://leetcode.com/problems/friend-requests-i-overall-acceptance-rate/discuss/103579/Following-up-questions.-Solved-Q1-how-to-solve-Q2

1. select if(d.req =0, 0.00, round(c.acp/d.req,2)) as accept_rate, c.month from 
   (select count(distinct requester_id, accepter_id) as acp, Month(accept_date) as month from request_accepted) c, 
   (select count(distinct sender_id, send_to_id) as req, Month(request_date) as month from friend_request) d 
   where c.month = d.month 
   group by c.month
   

 SELECT 
    `date`, (@csum := @csum + accept_rate) as daily_cumulative_accept_rate
FROM
    (
    SELECT
        `date`, SUM(IF(`action`='ACCEPT', `count`, 0)) / SUM(IF(`action`='SEND', `count`, 0)) as accept_rate
    FROM
        (
        SELECT 
            request_date as `date`, COUNT(DISTINCT sender_id, send_to_id) as `count`, 'SEND' as `action`
        FROM
            friend_request
        GROUP BY
            request_date
        UNION
        SELECT 
            accept_date as `date`, COUNT(DISTINCT requester_id, accepter_id) as `count`, 'ACCEPT' as `action`
        FROM
            request_accepted
        GROUP BY
            accept_date) as t1
    GROUP BY
        `date`
    ORDER BY
        `date`) as t2, (SELECT @csum := 0) as t3