[面试经验] FB data scientist, analytics 电面

----- Num of Unique contacts for each users ------
select u, sum(num) as cnts  from
(select u1 as u, count(distinct u2) as num  from table group by 1
union all
select u2 as u, count(distinct u1) as num from table group by 1) as t
group by 1;
------- Histogram -------
select cnts, count(distinct u) as num from
(select u, sum(num) as cnts  from
(select u1 as u, count(distinct u2) as num  from table group by 1
union all
select u2 as u, count(distinct u1) as num from table group by 1) as t
group by 1) as t1
group by 1;

onetwo 发表于 2017-12-1 11:58
这个好像不太对，我的思路是，你用个MAX（）去找每个user 给top partner发的信息数，同时SUM（）得到每个 ...

我觉得之前那个回答是对的，因为lz的题目里说先要找top partner，如果用max function找top partner应该没有用rank来找方便。当然如果问题是一步到位算那个ratio的话，那max似乎更容易理解一下，不过两种方法应该都是可以的。

Alice007 发表于 2017-11-22 12:18
. 1point3acres.com 刚才手抖给发错了，lz看一下我这个query写的对不？Select num_contact, count(*) as freq from( Select id, ...

记得还要限制个时间。。。

纯情小扣子 发表于 2018-3-22 06:26
记得还要限制个时间。。。

请教这个怎么加

stonephoenix 发表于 2018-1-10 09:33
修改后的第3问

想请问一下CODER PAD里面可以用WINDOW function吗，我刚去试了试，发现报错了

想请问一下楼主你是在在CODERPAD里面试的吗，coderpad可以用WINDOW function吗，我刚去试了试，发现报错了

select number_messages, count(*)
from
(select user_A as user, sum(number_messages) as number_messages
from Message
GROUP by user_A
union all
select user_B as user, sum(number_messages) as number_messages
from Message
GROUP by user_B) a
group by number_messages

-------------------------------------------------------------------------------------------------------
select
(select sum(number_messages) as n_msg_with_top_partner
from
(select user_id, friend_id, number_messages
ROW_NUMBER() over (partition by user_id order by number_messages desc) as ranknumber
from
(select user_A as user_id, user_B as friend_id, number_messages
from Message
union all
select  user_B as user_id, user_A as friend_id, number_messages
from Message) a
where ranknumber = 1) b)
/
(select sum(number_messages)
from Message)



请指教

linda.zhou 发表于 2018-1-30 05:41
----- Num of Unique contacts for each users ------
select u, sum(num) as cnts  from
(select u1 as ...

我和你的做法是类似的

SELECT t1.number_contact, count(*)
FROM
        (SELECT SUM(t0.count) number_contact
        FROM
                ((SELECT USER_A user, COUNT(USER_B) count
                FROM table
                        WHERE date = '2018-05-06'
                GROUP BY USER_A) l1

                UNION ALL

                (SELECT USER_B user, COUNT(USER_A) count
                FROM table
                        WHERE date ='2018-05-06'
                GROUP BY USER_B) l2) t0
..         GROUP BY t0.user )t1
GROUP BY t1.number_contact

misslost 发表于 2018-4-3 04:37
select number_messages, count(*)
from
(select user_A as user, sum(number_messages) as number_messa ...

感觉你对题目没理解对吧，题目是问每个user的contact有多少，而不是发了多少msg哦