脸家DS店面

SELECT ROUND(IFNULL(
(SELECT COUNT(*)
FROM (SELECT DISTINCT actor_uid, target_uid
FROM Friending
WHERE action = “accept_request”
AND date = “2018-01-01”) t1) /
(SELECT COUNT(*)
FROM (SELECT DISTINCT actor_uid, target_uid
FROM Friending
WHERE action = “send_request”
AND date = “2018-01-01”) t2)
, 0), 2) AS acceptance_rate;

謝謝樓主分享

holly3crane 发表于 2018-8-23 23:08
不知道我的想法是不是对的，可能是整体的逻辑和沟通出了bug？比如应该先和面试官讨论 怎么定义‘好’，然 ...

哈哈 我觉得有道理。我的确说了首先定义好，不过自问自答自己说了个答案....总之全程自问自答。。anyway...

holly3crane 发表于 2018-8-23 23:08
不知道我的想法是不是对的，可能是整体的逻辑和沟通出了bug？比如应该先和面试官讨论 怎么定义‘好’，然 ...

哈哈 我觉得有道理。我的确说了首先定义好，不过自问自答自己说了个答案....总之全程自问自答。。anyway...

Q1:
1. send a request on 2018-01-01, accept anytime
select ifnull(count(t2.*)/count(t1.*)) as acceptence_rate
(select distinct actor_uid, target_uid
from table where action = 'send_request' where date = '2018-01-01') as t1 left join
(select distinct actor_uid, target_uid
from table where action  = 'accept_request') as t2 on t1.actor_uid = t2.target_uid and t1.target_uid = t2.actor_uid

2. send and accept on 2018-01-01:
send a request on 2018-01-01, accept anytime
select ifnull(count(t2.*)/count(t1.*)) as acceptence_rate
(select distinct actor_uid, target_uid
from table where action = 'send_request' where date = '2018-01-01') as t1 left join
(select distinct actor_uid, target_uid
from table where action  = 'accept_request' where date = '2018-01-01') as t2 on t1.actor_uid = t2.target_uid and t1.target_uid = t2.actor_uid



Q2:
1. check other metrics, such as send request rate, day active user number, engagement user number 是不是有很大差异
2. 页面有没有feature发生了改变影响这个

选metrics衡量在newsfeed里面加friend recommendation这个feature好不? 以及各种follow ups, 比如如果发现test group的CTR 25%，这是好还是不好，选什么metric衡量user engagement level

crt,crp, day/monthly active user number, engagement user number
可以用假设检验么 在不在confidence interval， 算出成population是不是h很大
# of user send friend request/ ＃ of user visit newsfeed

自己随便写的 不知道对不对。。。欢迎指正

mathwyc 发表于 2018-8-9 00:29
根据不同的理解写了两个版本的query，请大家指正：
With t1 as (
Select distinct actor_uid, target_uid ...

我觉得你的第一个必须还是t1.actor_uid = t2.target_uid and t2.actor_uid = t1.target_uid
不然的话有可能acceptance rate > 100%
因为当天accpet的人可能大于当天send的人

1. 
   
2. -- Friends sent and accept on the same day
   
3. SELECT ROUND(COUNT(a.*)/NULLIF(COUNT(b.*),0)::float*100,2) AS accptance_rate
   
4. FROM
   
5. (SELECT DISTINCT actor_uid, target_uid, action
   
6. FROM Friending
   
7. WHERE date_trunc('day',time) = '2018-08-01' AND action = 'send_request') a
   -baidu 1point3acres
8. LEFT JOIN
   
9. (SELECT DISTINCT actor_uid, target_uid, action
   
10. FROM Friending
    
11. WHERE date_trunc('day',time) = '2018-08-01' AND action = 'accept_request') b
    
12. ON a.target_uid = b.actor_uid and a.actor_uid = b.target_uid
    
13. 
    
14. -- Friends accept on Aug 1st but send can be any day before
    
15. SELECT ROUND(COUNT(a.*)/NULLIF(COUNT(b.*),0)::float*100,2) AS accptance_rate
    
16. FROM
    
17. (SELECT DISTINCT actor_uid, target_uid, action
    
18. FROM Friending
    
19. WHERE date_trunc('day',time) <= '2018-08-01' AND action = 'send_request') a
    
20. LEFT JOIN
    
21. (SELECT DISTINCT actor_uid, target_uid, action
    
22. FROM Friending
    
23. WHERE date_trunc('day',time) = '2018-08-01' AND action = 'accept_request') b
    
24. ON a.target_uid = b.actor_uid and a.actor_uid = b.target_uid

复制代码

Thanks for sharing

求高手指点，这样写ok不？

SELCT date, sum(case when action='accept_request' then 1 else 0 end)/ sum(case when action in ('accept_request','send_request )then 1 else 0 end)
FROM A
WHERE date=2018-01-01