脸家DS店面

借朋友账号发个脸书面经回馈地里，题目跟地里看到的非常像。

开头：

自我介绍+介绍一个工作中的project

SQL：

Table: Friending

您好！
本帖隐藏的内容需要积分高于 111 才可浏览
您当前积分为 0。
使用VIP即刻解锁阅读权限或查看其他获取积分的方式

游客，您好！
本帖隐藏的内容需要积分高于 111 才可浏览
您当前积分为 0。
VIP即刻解锁阅读权限 或 查看其他获取积分的方式
Unlock interview details and practice with AI
Curated Interview Questions from Top Companies

您好！
本帖隐藏的内容需要积分高于 111 才可浏览
您当前积分为 0。
使用VIP即刻解锁阅读权限或查看其他获取积分的方式

游客，您好！
本帖隐藏的内容需要积分高于 111 才可浏览
您当前积分为 0。
VIP即刻解锁阅读权限 或 查看其他获取积分的方式
Unlock interview details and practice with AI
Curated Interview Questions from Top Companies

Product:

您好！
本帖隐藏的内容需要积分高于 166 才可浏览
您当前积分为 0。
使用VIP即刻解锁阅读权限或查看其他获取积分的方式

游客，您好！
本帖隐藏的内容需要积分高于 166 才可浏览
您当前积分为 0。
VIP即刻解锁阅读权限 或 查看其他获取积分的方式
Unlock interview details and practice with AI
Curated Interview Questions from Top Companies

求大米！

根据不同的理解写了两个版本的query，请大家指正：
With t1 as (
Select distinct actor_uid, target_uid
From Friending
Where action = ‘send_request’and date = ‘2018-01-01’
),

T2 as (
Select distinct actor_uid, target_uid
From friending
Where action = ‘accept_request’ and date = ‘2018-01-01’
)

Definition 1: request and accept do not need to be the same day

Select ifnull(
        (Select count(*) from t1) / (select count(*) from t2)
,0) as acceptance_rate

Definition 2: acceptance needs to match request

Select ifnull(count(t2.actor_id)/count(t1.acot_id), 0)
From t1 left join t2 on t1.actor_id = t2.target_id and t1.target_id = t2.actor_id

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

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

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

复制代码

请问q2 If you see the acceptance rate was 70% on 2018-01-05 and 50% on 2018-01-07, is there anything wrong? 这里是怎么回答的呀亲

Thank you

赞赞赞赞赞赞赞赞赞赞

想请教一下这里的acceptance rate如何定义的呢？是那一天所有accept request的action除以所有send request的action吗？有要求accept的必须是这一天的request吗？

请问这样做对不对呀
Select sum(case when t1.actor_uid is null then 0 else 1 end) / count(t2.actor_uid)
from
(Select distinct actor_uid, target_uid from friends
where action = ‘accept_request’and date = ‘2018-01-01’) t1
right join
(Select distinct actor_uid, target_uid from friends
where action = ‘send_request’and date = ‘2018-01-01’) t2
on t1.actor_uid = t2.target_uid and t2.actor_uid = t1.target_uid

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...