FB SMB Analyst电面

技术面是和HM直接面，统计和product／business题目根版上其他SMB面经很接近了SQL题不同，不

您好！
本帖隐藏的内容需要积分高于 188 才可浏览
您当前积分为 0。
使用VIP即刻解锁阅读权限或查看其他获取积分的方式

游客，您好！
本帖隐藏的内容需要积分高于 188 才可浏览
您当前积分为 0。
VIP即刻解锁阅读权限 或 查看其他获取积分的方式
Unlock interview details and practice with AI
Curated Interview Questions from Top Companies

r_id, friend_id, common_songs}

谢谢楼主分享~~我写了一下：

drop table if exists Table1;
drop table if exists Table2;
create table Table1(
  user_id int,
  song_id int
);
create table Table2(
  user_id int,
  friend_id int
);

insert into Table1 values(1, 1);
insert into Table1 values(1, 2);
insert into Table1 values(1, 3);
insert into Table1 values(1, 4);
insert into Table1 values(1, 5);
-baidu 1point3acresinsert into Table1 values(2, 1);
insert into Table1 values(2, 2);
insert into Table1 values(2, 6);
insert into Table1 values(4, 6);
insert into Table1 values(4, 7);
insert into Table1 values(4, 8);
insert into Table1 values(5, 1);
insert into Table1 values(5, 6);

insert into Table2 values(1, 2);
insert into Table2 values(1, 3);
insert into Table2 values(1, 4);
insert into Table2 values(2, 3);
insert into Table2 values(2, 4);
insert into Table2 values(2, 5);
insert into Table2 values(3, 5);
insert into Table2 values(4, 5);

select user_id, friend_id, count(song_id) as common_songs
from
(select t2.user_id, t2.friend_id as friend_id, t3.song_id
from Table1 t1
right join Table2 t2
on t1.user_id=t2.user_id
left join Table1 t3
on t2.friend_id=t3.user_id
and t1.song_id=t3.song_id) t
group by user_id, friend_id;

得到的output：
user_id,friend_id,common_songs
1,2,2
1,3,0
2,3,0
1,4,0
2,4,1
2,5,2
3,5,0
4,5,1

another way to solve：
select distinct table2.user_id, friend_id,  sum( case when t1.song_id=t3.song_id then 1 else 0 end) as num_common

from table2
left join table1 t1 on table2.user_id=t1.user_id
left join table1 t3 on table2.friend_id=t3.user_id

group by table2.user_id, friend_id

Select b.user_id,b.friend_id, sum(case when a.song_id in (select  c.song_id from table1 as c where c.user_id = b.user_id) then 1  else 0 end) 'common_songs'
from table1  as a right  join table2 as b on b.friend_id = a.user_id

忽略楼上:

Select b.user_id,b.friend_id, sum(case when a.song_id in (select  c.song_id from table1 as c where c.user_id = b.user_id) then 1  else 0 end) 'common_songs'
from table1  as a right  join table2 as b on b.friend_id = a.user_id
group by  b.user_id, b.friend_id

select t3.user_id,t3.friend_id, sum(case when t3.song_id = t3.friend_song then 1 else 0) as common_song
from (
select user_id, friend_id,song_id,
(select song_id from table t2 join table t1 on t1.friend_id = t2.user_id)
as friend_song
from table t1
) t3

补充内容 (2018-4-27 05:04):
group by t3.user_id