FB DS电面面经

SELECT
        advertiser_id,
        SUM(price) / SUM(spend) AS ROI
FROM advertiser
INNER JOIN user
ON advertiser.ad_id = user.ad_id
GROUP BY advertiser_id;

SQL 第一题有两种写法
select count(distinct a.advertiser_id)
from advertiser a
left join user u
on a.ad_id = u.ad_id
where u.price >0;
或者算total_price，然后count有多少advertiser 的total_price >0
select count(advertiser_id)
from
(select a.advertiser_id, sum(price) as total_price
from advertiser a
left join user u
on a.ad_id = u.ad_id
group by a.advertiser_id) sub
where total_price > 0;

第二题我写的有点不一样（感觉left join 前不需要group by，可以left join 后一起group by advertiser_id, ad_id)
这里我定义的metric是rate= (total_price - total_spent)/total_spent

select a. advertiser_id, a.ad_id, sum(a.spent) as total_spent, sum(u.price) as total_price, (sum(u.price) - sum(a.spent))/sum(a.spent) as rate
from advertiser a
left join user u
on a.ad_id = u.ad_id
group by a.advertiser_id, a.ad_id

感谢指正!

lancerts 发表于 2017-10-31 23:53
SQL 第一题有两种写法
select count(distinct a.advertiser_id)
from advertiser a

agree with this

我的第二题答案：
如果要aggregate到ad_level，则
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 ))from adv_info as a left join ad_info as b on a.ad_id =b.ad_id group by advertiser_id


补充内容 (2018-2-26 10:13):
一个是null要处理，还有一个是我感觉不能直接sum(spend).会对同一个advertiser的同一个adsum很多次

lancerts 发表于 2017-10-31 23:53
SQL 第一题有两种写法
select count(distinct a.advertiser_id)
from advertiser a

我们需要考虑如果spend is 0 或者 null 的情况吗？

n0thing233 发表于 2018-2-26 10:11
我的第二题答案：
如果要aggregate到ad_level，则
Ad_level:

Adv_level:

同一个ad 同一个advertiser 如果有很多条在table a 不是也是应该要sum 所有的吗 所以应该不存在同一个ad 同一个advertiser重复加的情况

这个题我怎么觉得要用right join啊，以 user table的行定基调

lancerts 发表于 2017-10-31 23:53
SQL 第一题有两种写法
select count(distinct a.advertiser_id)
from advertiser a

我怎么感觉第一题应该用inner join？只有同时出现在两个表中的ad_id才能算.谢谢指正！

jenny91515 发表于 2018-3-16 11:09
Adv_level:

同一个ad 同一个advertiser 如果有很多条在table a 不是也是应该要sum 所有的吗 所以应该 ...

我觉得会有重复加的问题，因为一个ad可能有多个user的price记录，left join 以后会重复多个adv_info里的数据，因为很可能ad_info里的数据比adv_info里的多。
spend应该默认是大于0的，不然就不应该出现在表里吧，不过price可能会是0，但是我觉得如果price都是null的话也没关系，sum()会自动排除null值，所以我觉得ifnull不是必须的

可能还要考虑spend的level是不是per ad level还是per usage level。如果对有dup的合并后的表一sum的话可能会重复加？所以可以用mean（spend）。
请指正啦啦啦