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

WITH follow_user_1 AS (
    SELECT *
    FROM follow
    WHERE user_id = 1
),
recommended_music AS (
    SELECT DISTINCT 
        music.id,
        music.music_name
    FROM follow_user_1
    JOIN music_likes 
        ON follow_user_1.follower_id = music_likes.user_id
    JOIN music 
        ON music_likes.music_id = music.id
    WHERE music_likes.music_id NOT IN (
        SELECT music_id
        FROM music_likes
        WHERE user_id = 1
    )
    ORDER BY music.id
)
SELECT 
    music_name
FROM recommended_music;