题解 | 网易云音乐推荐(网易校招笔试真题)

-- 先查找id = 1的人关注的人的id
with t1 as (
    select
        f.follower_id
        from 
        follow f
        where f.user_id = 1
),
-- 找到关注的人喜欢的音乐id，并且不在自己喜欢的音乐id的合集里
t2 as (
    select
        music_id
        from music_likes ml
        inner join t1
        on t1.follower_id = ml.user_id
       WHERE ml.music_id NOT IN (
        SELECT music_id 
        FROM music_likes 
        WHERE user_id = 1
    )
),

t3 as (
    select 
        distinct
        music_name,
        m.id
        from music m
        inner join t2 
        on t2.music_id = m.id 
        order by 
        m.id asc
)

select
    music_name
    from t3