FB DS 电面

点击这里快速回复？

hypothesis 是reject null?

遇到了相同的SQL题目，第二题里面spend不能只简单的sum。因为有多个ad_id和conversion，所以可以group by之后sum distinct？

n0thing233 发表于 2018-2-26 11:57
看了很多这道题的面经，有一点不明白为啥大家都用left join 呢？
给我感觉因为已经assume了 price: The us ...

我也不用left join 我觉得你写的才对呢...left之后还要看右边table是不是null

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

同意！ 我直接left join 会导致每一个advertiser_ID，和ad_ID不唯一，所以 spend是错的。

SELECT advertiser_id, SUM(IFNULL(rev,0)) / SUM(spend) AS ROI
FROM adv_info t1
LEFT JOIN
  (SELECT ad_id, SUM(price) AS rev
  FROM ad_info
  GROUP BY ad_id) AS t2
ON t1.ad_id = t2.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)),

同意！ 我直接left join 会导致每一个advertiser_ID，和ad_ID不唯一，所以 spend是错的。

SELECT advertiser_id, SUM(IFNULL(rev,0)) / SUM(spend) AS ROI
FROM adv_info t1
LEFT JOIN
  (SELECT ad_id, SUM(price) AS rev
  FROM ad_info
  GROUP BY ad_id) AS t2
ON t1.ad_id = t2.ad_id
GROUP BY advertiser_id

第一问：
select count(distinct adv.advertiser_id)/(select count(distinct advertiser_id from adv_info)
from adv_info adv join ad_info ad on adv.ad_id = ad.ad_id
这个问题认为不需要left join，只要在ad_info中出现的ad_id, 都认为是有点击量的，那么通过inner join，那些没有点击量的ad_id就自动filter out了，此时还能留在这个表里的，就是至少转化了一条广告的广告商
然后通过count+distinct来统计，有多少广告商有至少一条转化的广告
然后除以总广告商数量(select count(distinct advertiser_id from adv_info)，得到率

ROI：
按ad来aggregate
select advertiser_id, adv.ad_id, sum(price)/sum(spend)
from adv_info as adv left join ad_info as ad on adv.ad_id = ad.ad_id and advertiser_id ="xxxx"
group by advertiser_id,adv.ad_id
having sum(spend)>0

starfoe 发表于 2018-5-30 06:13
第一问：
select count(distinct adv.advertiser_id)/(select count(distinct advertiser_id from adv_inf ...

.google  и更正第二问，对于advertiser, 应先在ad_info上aggregate出每个ad_info的收入，再join到adv_info里，再按照advertiser进一步aggregate:

select adv.advertiser_id, sum(price)/sum(spend)
from adv_info adv left join
    (select ad_id, sum(price) price
     from ad_info
     group by ad_id) agg_ad
on adv.ad_id = agg_ad.ad_id
group by adv.advertiser_id
having sum(spend) > 0

最后要考虑下一个广告商在没有任何投资的影响，那么sum(spend) == 0, 除法可能会出问题，所以我用了having sum(spend) > 0 来把这些广告商剔除

不知道对不对啊求指正！

假设检验
uc: mean time spent on FB of the control group
ut: mean time spent on FB of the treatment group

构造了单边检验（这一点不太确定，感觉双边也可以？）

h0: uc = ut
ha: ut > uc

Assume alpha = 95%, 两组variance一样
用t检验
t =(ut-uc)/sqrt(var/1000+var/1000) = 2/sqrt(4.5/1000) = 30 >> 1.65
so reject h0

谢谢分享了