LeetCode 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?
create table friend_request(sender_id int,send_to_id int,request_date date)

insert into friend_request values (1,2,'2016-06-01')
insert into friend_request values (1,3,'2016-06-01')
insert into friend_request values (1,4,'2016-06-01')
insert into friend_request values (2,3,'2016-06-02')
insert into friend_request values (3,4,'2016-06-09')

select * from friend_request
select * from request_accepted

drop table request_accepted
create table request_accepted (requester_id int,accepter_id int,accept_date date)

insert into request_accepted values(1,2,'2016-06-03')
insert into request_accepted values(1,3,'2016-06-08')
insert into request_accepted values(2,3,'2016-06-08')
insert into request_accepted values(3,4,'2016-06-09')
insert into request_accepted values(3,4,'2016-06-10')


select 
round(
isnull(
(
     cast(
	       (select count(*) from 
		                    (select distinct requester_id, accepter_id from request_accepted) 
			as b)
	as float)
/
(select count(*) from (select distinct sender_id,send_to_id from friend_request) as a))
,0)
,2) as accept_rate




with cte as (

select distinct requester_id, accepter_id,accept_date from request_accepted

) select distinct c.requester_id, c.accepter_id from cte c join request_accepted a on MONTH(c.accept_date)=MONTH(a.accept_date)

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值