值得分享的一道Facebook SQL题 好友请求率+Follow Up

**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?

  ROUND(
        IFNULL(
          (SELECT count(*) FROM (SELECT DISTINCT requester_id, accepter_id FROM request_accepted) AS A)
          /
          (SELECT count(*) FROM (SELECT DISTINCT sender_id, send_to_id FROM friend_request) AS B)
          ,0)
  ,2) AS accept_rate
select count( distinct col1 , col2 , col3 , .......) from table
但是，这样是不允许的???，因为count是不能统计多个字段的，虽然distinct是可行的。

有种比较直接的方法就是把消除重复后在统计查询:

```select count(*) from (select distinct col1 ,col2 , col3 from table)A
```

但是，我的MYSQL版本可以的。https://dba.stackexchange.com/qu ... th-multiple-columns

为了回答Follow Up题目，我亲测。
```
create table demo.request_accepted(
  requester_id int,
  accepter_id varchar(80),
  accept_date MEDIUMTEXT
);

insert into demo.request_accepted values('1','2','2016-06-03');
insert into demo.request_accepted values('1','3','2016-06-08');
insert into demo.request_accepted values('2','3','2016-06-08');
insert into demo.request_accepted values('3','4','2016-06-09');
insert into demo.request_accepted values('3','4','2016-06-10');
```
Follow Up1.Can you write a query to return the accept rate but for every month?
SELECT ROUND(IFNULL((A.accept_num/B.request_num),0),2) AS accept_rate, A.accept_m AS month
FROM
(SELECT COUNT(DISTINCT requester_id, accepter_id) AS accept_num, MONTH(accept_date) AS accept_m FROM demo.request_accepted GROUP BY accept_m) A,
(SELECT COUNT(DISTINCT sender_id, send_to_id) AS request_num, MONTH(request_date) AS request_m FROM demo.friend_request GROUP BY request_m) B
WHERE A.accept_m = B.request_m
GROUP BY A.accept_m;
Result
{
    "headers": ["accept_rate", "month"],
    "values": [[0.80, 6]]
}

Follow-up 2: return the cumulative accept rate for every da
SELECT ROUND(COUNT(DISTINCT requester_id, accepter_id) / COUNT(DISTINCT sender_id, send_to_id),2) AS accept_rate, data_table.dates
FROM demo.request_accepted acp, demo.friend_request req,
(SELECT request_date AS dates FROM demo.friend_request
UNION
SELECT accept_date FROM demo.request_accepted
ORDER BY dates) data_table
WHERE acp.accept_date <= data_table.dates AND req.request_date <= data_table.dates
GROUP BY data_table.dates;
Result
{
    "headers": ["rate", "dates"],
    "values": [[0.25, "2016-06-03"], [0.75, "2016-06-08"], [0.80, "2016-06-09"], [0.80, "2016-06-10"]]
}

大家自己写的时候可以把demo去掉。

还差几粒大米可以看面经了，各位大佬给我加点大米吧，谢谢！

谢谢加分的大佬！

谢谢各位大佬，还差一分了。明天就可以看面筋啦。跪谢

想问一下像这样自己新建一个table测试的话，是在哪里测试呀，我就是在leetcode上但不知道为什么没法用上面的insert into语句新建table，老是报错

jack晓峰 发表于 2021-05-22 21:10:34
想问一下像这样自己新建一个table测试的话，是在哪里测试呀，我就是在leetcode上但不知道为什么没法用上面的insert into语句新建table，老是报错

自己下个mysql就行了