https://leetcode-cn.com/problems/page-recommendations-ii/

1892.png

select a.user1_id user_id ,a.page_id page_id ,count(distinct a.user2_id)friends_likes  from(
select distinct a.user1_id,a.user2_id,c.page_id
from 
(select user1_id,user2_id from Friendship union select user2_id,user1_id from Friendship)a 
left join Likes b on a.user1_id=b.user_id
left join Likes c on a.user2_id=c.user_id
where b.page_id!=c.page_id
)a left join (
    select a.user1_id,a.user2_id,b.page_id
    from (select user1_id,user2_id from Friendship union select user2_id,user1_id from Friendship) a,
    (
        select a.page_id,group_concat(user_id separator ',')user_list from(
            select page_id  from Likes 
            group by page_id 
            having count(distinct user_id)>1
        )a left join Likes b on a.page_id=b.page_id
        group by a.page_id 
    )b where find_in_set(a.user1_id,b.user_list)>0 and find_in_set(a.user2_id,b.user_list)>0
)b on a.user1_id=b.user1_id and a.user2_id=b.user2_id and a.page_id=b.page_id
where b.page_id is null 
group by a.user1_id,a.page_id 
# order by a.user1_id,a.page_id