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

SELECT SUM(max_msg)
FROM
        (SELECT USER_A user, MAX(number_messages) max_msg
        FROM table
                WHERE date = '2018-05-06'
        GROUP BY USER_A

        UNION ALL

        SELECT USER_B user, MAX(number_messages) max_msg
        FROM table
                WHERE date = '2018-05-06'
        GROUP BY USER_B) /

SELECT SUM(number_messages) all_msg_all_contacts

大家帮忙看看对吗

补充内容 (2018-5-9 14:18):
SELECT SUM(b.final_max_msg)
FROM

        (SELECT a.user user, MAX(a.max_msg) final_max_msg
        FROM
                (SELECT USER_A user, MAX(number_messages) max_msg
                FROM table
                        WHERE date = '2018-05-06'
                GROUP BY...

sagaam 发表于 2018-5-9 14:11
SELECT SUM(max_msg)
FROM
        (SELECT USER_A user, MAX(number_messages) max_msg

SELECT SUM(b.final_max_msg)
FROM

        (SELECT a.user user, MAX(a.max_msg) final_max_msg
        FROM
                (SELECT USER_A user, MAX(number_messages) max_msg
                FROM table
                        WHERE date = '2018-05-06'
                GROUP BY USER_A

                UNION ALL

                SELECT USER_B user, MAX(number_messages) max_msg
                FROM table
                        WHERE date = '2018-05-06'
                GROUP BY USER_B) a ) b

/SELECT SUM(number_messages) all_msg_all_contacts

在union all以后应该还要再做一次max，这样才能找到每个user的top partener，不知道我是不是理解正确了

Histogram -

----table info----------

create table message(
  user1_id int,
  user2_id int,
  n_msg int
);

-------------------------

select n_contact, count(user_id)
  from
  (select user_id, count(*) as n_contact
     from
    (select user1_id as user_id from message
    union all
    select user2_id as user_id from message)a
  group by user_id)b
group by n_contact;

Alice007 发表于 2017-11-23 00:54
第三个sql题目：
请lz/层主们帮忙看看对不对哈

我感觉这个写法挺好的，最后应该再group by u1吧

问个简单的问题哈：
楼主描述了 每行是一对unique user pair之间在某天发的消息数量，所以会存在重复记录的情况所以要用union all？不然是不是用union 就可以？
date | u1 | u2 | n_msg
Jan1  user1  user2  3
Jan1  user2  user1  3

我感觉Possion分布。。还是指数分布呢？

"写个query得到某一天用户发消息朋友数量的distribution，就是output出两列，X: number of unique contacts for each user; Y: number of user with this many contacts。问你觉得这个distribution会长什么样子，为什么" 请问大家这个到底是什么distribution啊？

SELECT floor(sub2.n_friends_sum/10)*10 AS bin, count(*) AS cnt
FROM
(SELECT date, sender, sum(n_friends) AS n_friends_sum
.--  FROM
    (SELECT date, u1 AS sender, COUNT(DISTINCT u2) AS n_friends
     FROM Table AS tbl1 GROUP BY u1
     UNION ALL
     SELECT date, u2 AS sender, COUNT(DISTINCT u1) AS n_friends
     FROM Table AS tbl2 GROUP BY u2) AS sub
   GROUP BY sender) AS sub2
WHERE sub2.date=CURDATE()
GROUP BY 1
ORDER BY 1

尝试一下，有错请帮忙支出，感激~
Q2:
SELECT date,contact_num,COUNT(user_A) AS users_num
FROM
    (
    SELECT date,user_A,COUNT(DISTINCT user_B) as contact_num
    FROM
        (
        SELECT * FROM table
        UNION ALL
        SELECT date,user_B,user_A,number_messages
        ) w1
    WHERE number_message>0
    GROUP BY date,user_A
    ) w2
GROUP BY date,contact_num

Q3:

SELECT date,SUM(CASE WHEN rank=1 THEN 1 ELSE 0 END)/SUM(number_messages) AS top_partner_percentage
    FROM
        (
        SELECT date,user_A,number_messages,RANK() OVER (PARTITION BY date,user_A ORDER BY number_messages DESC) AS rank
            FROM
                (
                SELECT * FROM table
                UNION ALL
                SELECT date,user_B,user_A,number_messages
                ) w1
            WHERE number_messages>0
        ) W2
    GROUP BY date