11.3 FB Data Scientist Interview

Job Hunting Interview
, , , ,
1

11.3 FB Data Scientist Interview
攒rp 不过心里知道面的不好。。。而且和大家不一样的是 我不会SQL 所以用的python。。。
1。Data Manipulation

#Table name: content_actions user_id content_id content_type (’‘post’’, ‘‘photo’’, ‘‘comment’’) #story: post or phototarget_id
1 5 ‘‘post’’ '‘Null’'17 6 ‘‘photo’’ '‘Null’'16 20 ‘‘comment’’ 5

1). Generate a distribution for the #comments per story.2). Does this account for stories with 0 comments?
2. Product Sense

How would you make a list of recommended restaurant to Facebook users?What information would you need?If you have all those information, how could you build a model to get a list of top recommendations?how to test this model?what metric will you use?how could you get these data?how could you test if this product is working or not?
大概就是这样 还木有消息

1 Like
2

这道题难道不是：
SELECT number_of_comment, COUNT(*) freq
FROM
(SELECT content_id, SUM(CASE content_type WHEN ‘‘comment’’ THEN 1 ELSE 0) number_of_comments
FROM table
WHERE content_type in (’‘post’’, ‘‘photo’’)
GROUP BY content_id)
GROUP BY number_of_comment

3

select cnt, count(1)
from
(select target_id, count(1) as cnt
from content_actions
where content_type = ‘‘comment’’ and traget_id not null
group by target_id) a
group by cnt

4

SELECT number_of_comment, COUNT(*) freq
FROM
(SELECT content_id, SUM(CASE content_type WHEN ‘‘comment’’ THEN 1 ELSE 0) number_of_comments
FROM table
WHERE content_type in (’‘post’’, ‘‘photo’’)
GROUP BY content_id)
GROUP BY number_of_comment

5

谢谢解释。大概sql是这么个意思吧。这样的话，没有点评的故事也会输出。
(这里应该有个假设是content_id是unique的吧，如果是user_id + content_id是unique的，就得稍微改改。)

select
comments,
count(story) as dis_cnt
from
(select
a.content_id as story,
case when b.target_id is not NULL then b.cnts else 0 end as comments
from content_actions a
where content_type in (“post”, “photo”)
left join
(select
target_id, count(user_id) as cnts
from content_actions
where content_type = “comment”
group by 1) b
on a.content_id = b.target_id)
group by 1

6

楼主，能麻烦再介绍下第一个题么，target_id是干嘛用的。story 是 content么？

7

大概是这样。首先我们把发一个post或者发一个photo定义为一个story。对于每一个story我们都有相对应的user_id content_id. 然后如果有人comment了某个story，我们会相应的有target_id用来表明他这个comment去了哪个story下面。

#Table name: content_actions
user_id
content_id
content_type (‘‘post’’, ‘‘photo’’, ‘‘comment’’) #story: post or photo
target_id

8

谢谢楼主！

10

select
comments,
count(distinct content_id) as stories
from
(select
content_id,
zeroifnull(comments) as comments
from (select distinct content_id
from content_actions
where content_type in (’‘photo’’, ‘‘post’’)
) a
left join (select target_id, count(user_id) as comments
from content_actions
where content_type in (’‘comments’’)
group by 1
) b
on a.content_id = b.target_id
) A
group by 1

11

谢谢楼主，非常有用！请问楼主第二题答题思路是怎样的呢

12

select distinct c.content_id,
(case when t1.counts is null then 0 else t1.counts end ) as counts
from content_actions as c left join
(select target_id as content_id, count(*) as counts
from content_actions
where content_type = “comment”
group by target_id) as t1
on c.content_id = t1.content_id
where content_type != “comment”;