FB DS电面面经

LZ刚刚面完就来发面经啦，攒人品攒人品！
1. given two tables, advertizer: advertiser_id, ad_id, spend; user: user_id, ad_id, price
(1). 要求算有多少个advertizer has at least 1 conversion
(2). 要求根

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

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

entify fake high school names without external data. 这个地里很多面经都提到了

求onsite！

blessssssssssss 楼主~~ sql 求讨论指导~~ 谢谢！
# assuming spend column in advertizer table means spend of advertizer on the add.
# assuming price column in user table means the price user paid through the ad_id.
Q1:
select count(distinct advertiser_id)
from advertizer aa
inner join user uu
on aa.ad_id = uu.ad_id
where uu.price > 0

Q2
select advertiser_id, ad_id, case when advertiser_spending == 0 then NULL else   round( user_spending/advertiser_spending ,2) end as ROI

from
(select advertiser_id, ad_id, sum(spend) as advertiser_spending
from advertizer
group by advertiser_id, ad_id
) t1
left join
(
select ad_id, sum(price) as user_spending
from user
group by ad_id
) t2
on t1.ad_id = t2.ad_id

(1) Ads whose ad_id don't appear in the user table is not converted. We can use INNER JOIN here.

1. SELECT (COUNT DISTINCT advertiser_id) AS num_advertiser
   
2. FROM
   
3. advertiser
   
4. INNER JOIN user
   
5. ON advertiser.ad_id = user.ad_id

复制代码

(2) Two cases here:
No.1: ROI on every ad_id

1. SELECT advertiser_id, ad_id, SUM(IFNULL(price,0)) / spend
   
2. FROM advertiser
   
3. LEFT JOIN user
   
4. ON advertiser.ad_id = user.ad_id
   
5. GROUP BY advertiser_id, ad_id

复制代码

No.2: ROI for every advertiser_id

1. SELECT advertiser_id, SUM(IFNULL(rev,0)) / SUM(spend) AS ROI
   
2. FROM
   
3. advertiser
   
4. LEFT JOIN
   
5. (SELECT ad_id, SUM(price) AS rev
   
6. FROM user
   
7. GROUP BY ad_id) tbl1
   
8. ON advertiser.ad_id = tbl1.ad_id
   
9. GROUP BY advertiser_id

复制代码

我的第二题答案：
如果要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很多次

bless，有考coding吗？

请问楼主 SQL题哪个column能看出conversion呀？
那个user table的price是这个user的价格？如果在Use table 里面 groupby(ad_id).sum(price) 会等于advertizer里面的ad_id, spend吗？

如何定义conversion，楼主是怎么写的query呀？ROI怎么定义的？

ccciel2 发表于 2017-9-10 22:56
请问楼主 SQL题哪个column能看出conversion呀？
那个user table的price是这个user的价格？如果在Use tabl ...

conversion是指如果有user点进广告付了钱 那么user table就会有一条记录 user.price指的是这个user花了多少钱这样

fishy 发表于 2017-9-9 14:25
bless，有考coding吗？

没，他家不是很有名的只考SQL么。。。

lz 看看sql写的差不多嘛~~

1 select d.department _name from employee e join department d on e.department_id = d.department_id group by d.department _name having sum(e.salary) > 40000


2 select concat(e.first_name,’ ’, e.second_name) as name, e.salary  from employee e join department d
On e.department_id = d.department_id
where e.salary, d.department_id  in
(select max(e.salary),  d.department_id  from employee e join department d on e. Department_id = d.department_id group by d.department_id  )

额  回复错帖子了。。。 忽视我 嘤嘤嘤

mark一下