一亩三分地论坛

 找回密码
 获取更多干货,去instant注册!

一亩三分地官方iOS手机应用下载
查看: 1864|回复: 11
收起左侧

fb data scientist电面跪经

[复制链接] |试试Instant~ |关注本帖
LuckyGemini 发表于 2016-12-1 07:30:54 | 显示全部楼层 |阅读模式

2016(10-12月) 分析|数据科学类 硕士 实习@Facebook - Other - 技术电面 |Failfresh grad应届毕业生

注册一亩三分地论坛,查看更多干货!

您需要 登录 才可以下载或查看,没有帐号?获取更多干货,去instant注册!

x
对sql不是很熟,然后又很紧张,当场扑街了
问的问题是,给两个table 一个是request(sender_id,request_id,time)
还有一个是accepts(accept_id, request_id,time)
求acceptance rate

我用inner join on sender_id 他说不对…….鏈枃鍘熷垱鑷1point3acres璁哄潧
然后求谁的朋友最多,我说从accepts table里选accept_id出现最多的
他说不对……
于是我就跪了……呜呜呜呜

评分

2

查看全部评分

miles0302 发表于 2017-2-4 16:58:56 | 显示全部楼层
关注一亩三分地公众号:
Warald_一亩三分地
小塔 发表于 2016-12-22 11:47
这样对么?

1, select count(accept_id) from accepts/(select count(request_id) from requests as rati ...

一个request id 没有被accept 就不会有accept id。所以要request left join accepts. Left join 后如果一个request_id对应的行出现accept id 是null的话就是这个request没有被accept。不是null的行数除以总行数就是acceptance rate
回复 支持 1 反对 0

使用道具 举报

houqingniao 发表于 2016-12-1 08:13:12 | 显示全部楼层
关注一亩三分地微博:
Warald
不应该on request_id么
回复 支持 反对

使用道具 举报

MulinZz 发表于 2016-12-1 10:27:41 | 显示全部楼层
求问sql 的题目要如何准备。
回复 支持 反对

使用道具 举报

wendyhz2hu 发表于 2016-12-1 11:15:29 | 显示全部楼层
楼主我也同跪,遇到同样的问题
求职神器indeed - 在全球最大的求职网站找找适合你的工作?
回复 支持 反对

使用道具 举报

在浙里 发表于 2016-12-1 11:23:46 | 显示全部楼层
楼主是什么专业的?
回复 支持 反对

使用道具 举报

linjunch 发表于 2016-12-1 12:26:41 | 显示全部楼层
1,select sum(case when accept_id is not Null the 1 else 0 end)/count(*) from request as r left join accepts as a on request_id and time
2,t1.request_id,t1.cnt1+t2.cnt2 as cnt_tot from (sel sender_id, count(*) as cnt1 from  request as r left join accepts as a on request_id and time where a.accept_id is not Null group by 1) as t1  left join (sel accept_id, count(*) as cnt2 from  request as r1 left join accepts as a1 on request_id and time where a1.accept_id is not Null group by 1)  as t2 on t1.sender_id=t2.accpet_id
order by 1 desc
回复 支持 反对

使用道具 举报

linjunch 发表于 2016-12-1 12:33:14 | 显示全部楼层
linjunch 发表于 2016-12-1 12:26
1,select sum(case when accept_id is not Null the 1 else 0 end)/count(*) from request as r left join  ...


1,select sum(case when accept_id is not Null the 1 else 0 end)/count(*) from request as r left join accepts as a on request_id and time
2,t1.request_id,t1.cnt1+t2.cnt2 as cnt_tot from (sel sender_id, count(*) as cnt1 from  request as r left join accepts as a on request_id and time where a.accept_id is not Null group by 1) as t1  outer join (sel accept_id, count(*) as cnt2 from  request as r1 left join accepts as a1 on request_id and time where a1.accept_id is not Null group by 1)  as t2 on t1.sender_id=t2.accpet_id
order by 2 desc 改了一下
回复 支持 反对

使用道具 举报

lha_1313 发表于 2016-12-1 14:02:19 | 显示全部楼层
fb的电面题三年都不带变的么。。
回复 支持 反对

使用道具 举报

raypeng 发表于 2016-12-1 14:13:04 | 显示全部楼层
我感觉面的时候我sql也很卡壳 结果过了 lz了解fb的ds么 希望可以聊聊
回复 支持 反对

使用道具 举报

小塔 发表于 2016-12-22 11:47:37 | 显示全部楼层
这样对么?

1, select count(accept_id) from accepts/(select count(request_id) from requests as ratio;

2, sender_id出现的次数 + accept_id出现的次数 full outer join on sender_id=accept_id ?
回复 支持 反对

使用道具 举报

kurokubs 发表于 3 天前 | 显示全部楼层
drop table if exists request;. from: 1point3acres.com/bbs
drop table if exists accept;

create table request(. 1point 3acres 璁哄潧
  sender_id int,. visit 1point3acres.com for more.
  request_id int,
. Waral 鍗氬鏈夋洿澶氭枃绔,  time int. 鐣欏鐢宠璁哄潧-涓浜╀笁鍒嗗湴
);.1point3acres缃

create table accept(
  accept_id int,
  request_id int,
  time int
);. from: 1point3acres.com/bbs
. 1point 3acres 璁哄潧

insert into request Values(1,13,101);
insert into request Values(1,14,102);
insert into request Values(2,113,1111);
insert into request Values(3,115,103);. 1point 3acres 璁哄潧
insert into request Values(3,117,12321);.鐣欏璁哄潧-涓浜-涓夊垎鍦
insert into request Values(3,118,12311);

insert into accept Values(5,13,101);
insert into accept Values(6,14,102);
insert into accept Values(7,115,103);
insert into accept Values(3,113,1111);
insert into accept Values(7,118,12311);. 鍥磋鎴戜滑@1point 3 acres

--Q1;


SELECT SUM(CASE WHEN accept_id IS NOT NULL THEN 1 ELSE 0 END)/ CAST(COUNT(*) AS DECIMAL(9,2)) AS accept_rate
FROM
request a
LEFT JOIN-google 1point3acres
accept b
ON a.request_id = b.request_id AND a.time = b.time
;
. Waral 鍗氬鏈夋洿澶氭枃绔,
-- Q2;


SELECT t1.sender_id, (CASE WHEN t1.cnt IS NULL THEN 0 ELSE t1.cnt END) + (CASE WHEN t2.cnt IS NULL THEN 0 ELSE t2.cnt END) AS friend
FROM
(SELECT r1.sender_id, COUNT(*) as cnt FROM request r1 LEFT JOIN accept a1 on r1.request_id=a1.request_id AND r1.time = a1.time WHERE a1.accept_id IS NOT NULL GROUP BY r1.sender_id) t1-google 1point3acres
FULL OUTER JOIN
(SELECT a2.accept_id, COUNT(*) as cnt FROM request r2 LEFT JOIN accept a2 on r2.request_id=a2.request_id AND r2.time=a2.time WHERE a2.accept_id IS NOT NULL GROUP BY a2.accept_id) t2
ON t1.sender_id = t2.accept_id
WHERE t1.sender_id IS NOT NULL
ORDER BY 2 DESC LIMIT 1
;



回复 支持 反对

使用道具 举报

本版积分规则

请点这里访问我们的新网站:一亩三分地Instant.

Instant搜索更强大,不扣积分,内容组织的更好更整洁!目前仍在beta版本,努力完善中!反馈请点这里

关闭

一亩三分地推荐上一条 /5 下一条

手机版|小黑屋|一亩三分地论坛声明 ( 沪ICP备11015994号 )

custom counter

GMT+8, 2017-3-31 06:44

Powered by Discuz! X3

© 2001-2013 Comsenz Inc. Design By HUXTeam

快速回复 返回顶部 返回列表