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

1. with msg_table as (
   
2. SELECT u1,u2,sum(n_msg） as Total_n_msg
   
3. FROM
   
4.   (select u1,u2,n_msg from table
   
5.    union all
   
6.   select u2 as u1, u1 as u2,n_msg from table
   
7.   ) temp
   
8.   group by u1, u2
   
9. )
   
10. 
    
11. with temp as (
    
12. select u1,u2 as top_partner from msg_table
    
13. where row_number()over()(partition by u1,order by Total_n_msg desc) =1)
    
14. 
    
15. 
    
16. select u1,first(top_partner), max(n_msg)/sum(n_msg)
    
17. from msg_table join temp on temp.u1=msg_table.u1
    
18. group by u1
    

复制代码

fm3835 发表于 2018-6-21 00:44
SELECT floor(sub2.n_friends_sum/10)*10 AS bin, count(*) AS cnt
FROM
(SELECT date, sender, sum(n_ ...

SELECT SUB1.bin_floor, SUB1.bin_ceil, COUNT(1) AS cnt
FROM(
        SELECT SUB.user_A,
               COUNT(DISTINCT SUB.user_B) AS n_contacts,
               FLOOR(COUNT(DISTINCT SUB.user_B)/BIN)*BIN AS bin_floor,
               FLOOR(COUNT(DISTINCT SUB.user_B)/BIN)*BIN+BIN AS bin_ceil
        FROM
                (SELECT user_A, user_B
                 FROM table
                 UNION
                 SELECT user_B AS user_A, user_A AS user_B
                 FROM table
                 WHERE date=CURDATE()) AS SUB
        GROUP BY SUB.date, SUB.user_A) AS SUB1
GROUP BY SUB1.bin_floor, SUB1.bin_ceil

jiangh52 发表于 2017-12-31 16:03
（非米DA）
1-2, 这种distribution大多数情况都是接近于指数分布的形状吧

请问为什么是指数分布呢 谢谢

stonephoenix 发表于 2018-1-10 09:03
正在准备面试，所以把自己的答案写一下，有错的话请大家一定要指出来啊！多谢！
（2）写个query得到某一天 ...

多谢