FB DS 电面

找工作第一枪，肯定跪了。贴出来回馈大家

（1）主要问题是communication，不太能get面试官口音，不停的couldyou re

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

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

e:7.5pt">-涓

看了很多这道题的面经，有一点不明白为啥大家都用left join 呢？
给我感觉因为已经assume了 price: The user spend through this ad (Assume all prices in this column >0)
所以只要inner join 就够了，留下的就一定有convertion.
我的代码：
Q1:
Select (distinct advertiser_id)/(select count(distinct advertiser_id) from adv_info)
from adv_info  as a
inner join ad_info  as b
on a.ad_id = b.ad_id
Q2:
可以aggregate到两个level,要跟interviewer搞清楚他是要advertiser的ROI还是advertiser每个ad的ROI.
Ad_level:
Select  a.advertiser_id, a.ad_id, (sum(ifnull(b.price,0))/a.spend)
from adv_info as a
left join ad_info as b
on a.ad_id = b.ad_id
group by advertiser_id, ad_id
如果要aggregate到advertiser_level,则：
Adv_level:
Select  a.advertiser_id, (sum(ifnull(b.price,0))/(select sum(spend)
from adv_info as c  where c.advertiser_id = a.advertiser_id ))'ROI'
from adv_info as a
left join ad_info as b
on a.ad_id =b.ad_id
group by advertiser_id

maohappy2013 发表于 2018-3-1 23:47
select adv.advertiser_id,
     cost = sum(spend),
     rev = sum(isnull(ad.price,0)),

同意，应该先aggregate再join啊。
ROI：
select sum(revenue)/sum(spend) as ROI
from
        (select ads_id, sum(spend) as spend
        from adv_info  a
        where date= current_date()
        group by 1
        ) s
left join
        (select ads_id, sum(user_spend) as revenue
        from ad_info b
        where date = current_date()
        group by 1
        ) r
. check 1point3acres for more.on s.ads_id = r.ads_id

stonephoenix 发表于 2018-1-9 09:42
-- Q1: The fraction of advertiser has at least 1 conversion?-- Q2 What metrics would you show to adv ...

select adv.advertiser_id,
     cost = sum(spend),
     rev = sum(isnull(ad.price,0)),
     ROI = sum(spend)/sum(isnull(ad.price,0))
from adv_info adv
left join ad_info ad.info ad
on adv.ad_id = ad.ad_id
group by adv.advertiser_id
Question:
如果left join 之后table就会变成:
advertiser id  ad id spend user id price
1                   1      1000     1    5
1                   1       1000     2    10
这种情况下上面的ｑｕｅｒｙ岂不是算了两遍 ad spend
sum(spend) = 2000 sum(price) = 15 但实际上在这个ad_id = 1上只花了１０００

2.         SQL部分

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

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

Follow up: The conversion rate for the experiment is 10%. And the engineer team rollout this feature globally, Estimate the total number of user use this feature.  

多谢lz详细面经，请问是hr 主动reachout的吗？lz现在的工作是data scientist吗？谢谢啦

maomaochong 发表于 2018-1-6 07:14
多谢lz详细面经，请问是hr 主动reachout的吗？lz现在的工作是data scientist吗？谢谢啦

对，是hr 主动reachout。我一直在药厂做biostats，考的内容和我平时工作内容差别还是挺大的

LZ收到通知了么...我估计如果是我的话product估计和LZ的答案差不多
variance是1.5那个是他直接告诉你的么 还是会问你怎么得到variance...?

没收到通知啊，所以肯定是跪了。。。variance是他看我墨迹了半天不知道从何入手然后直接告诉我的，他的expectation应该是我自己问他variance

lz看sql 差不多是这样吗

1 select
(select count(distinct advertiser_id) from adv_info left  join ad_info on adv_info.ad_id = ad_info.ad_id where ad_info.price > 0  ) / (select count(distinct advertiser_id) from adv_info )


2 ROI   要分别根据 advertise_id和 ad_id  group by 两次


select advertiser_id, sum(ad_spend) as adv_spend, ifnull(sum(ad_gain) , 0 ) as adv_gain, ifnull(sum(ad_gain)/sum(ad_spend) ) as ROI
from
(Select advertiser_id, adv_info.ad_id, sum(spend) as ad_spend, sum(price) as ad_gain
from adv_info left  join ad_info on adv_info.ad_id = ad_info.ad_id group by adv_info.ad_id)
group by advertiser_id

-- Q1: The fraction of advertiser has at least 1 conversion?

1. select count(distinct advertiser_id)/(select count(distinct advertiser_id from adv_info)
   
2. from adv_info adv
   
3. left join ad_info ad
   
4. on adv.ad_id = ad.ad_id
   
5. where ad.price > 0

复制代码

-- Q2 What metrics would you show to advertisers (其实就是在问ROI), 用SQL实现

1. select adv.advertiser_id,
   
2.      cost = sum(spend),
   
3.      rev = sum(isnull(ad.price,0)),
   
4.      ROI = sum(spend)/sum(isnull(ad.price,0))
   
5. from adv_info adv
   
6. left join ad_info ad
   
7. on adv.ad_id = ad.ad_id
   
8. group by adv.advertiser_id

复制代码

rochellehx 发表于 2018-1-9 06:37
没收到通知啊，所以肯定是跪了。。。variance是他看我墨迹了半天不知道从何入手然后直接告诉我的，他的expe ...

不要灰心。。。
我之前也有过电面了三四天才收到通知的结果过了
虽然最后onsite还是挂了