题解 | 25年网易云音乐-歌手新歌首发后听众分层次日留存与时段活跃矩阵

-- 首日听众
with a as(
    select 
        user_id,
        tp.song_id,
        artist_id,
        min(play_start) as play_start_min
    from t_play tp
        join t_song ts using(song_id)
    where date(play_start)=date(release_time)
        and play_sec>=duration_sec
    group by tp.song_id,user_id,artist_id
),
-- 首日早中晚分段
b as(
    select 
        artist_id,
        case  when HOUR(play_start_min) between 6 and 11 then 'morning'
        when HOUR(play_start_min) between 12 and 17 then 'afternoon'
        else 'night' end as time_slot,
        song_id,
        user_id,
        play_start_min
    from a
),
-- 回流用户播放记录标记
c as(
    select 
        b.artist_id,
        b.user_id,
        b.song_id,
        time_slot,
        max(case when date(play_start)=DATE_ADD(date(b.play_start_min), INTERVAL 1 DAY) then 1 else 0 end) as D1,
        max(case when date(play_start)=DATE_ADD(date(b.play_start_min), INTERVAL 3 DAY) then 1 else 0 end) as D3,
        max(case when date(play_start)=DATE_ADD(date(b.play_start_min), INTERVAL 7 DAY) then 1 else 0 end) as D7
    from b 
        join t_play on b.user_id=t_play.user_id
        join t_song on t_play.song_id=t_song.song_id
    where play_sec >= duration_sec and t_play.song_id in (select song_id from t_song where t_song.artist_id=b.artist_id)
    group by b.artist_id,b.user_id,time_slot,b.song_id
),
-- 首日基数
d as(
    select 
        artist_id,
        time_slot,
        count(*) as base_user_cnt
    from b
    group by artist_id,time_slot 
),
-- 回流数量
e as(
    select 
        artist_id,
        time_slot,
        sum(D1) as D1_number,
        sum(D3) as D3_number,
        sum(D7) as D7_number
    from c
    group by artist_id,time_slot
)
select 
    d.artist_id,
    time_slot,
    base_user_cnt,
    ifnull(round(D1_number/base_user_cnt,2),0.00) as d1_rate,
    ifnull(round(D3_number/base_user_cnt,2),0.00) as d3_rate,
    ifnull(round(D7_number/base_user_cnt,2),0.00) as d7_rate
from d left join e using(artist_id,time_slot)
order by d7_rate desc,base_user_cnt desc,d.artist_id,case time_slot when 'morning' then 1 when 'afternoon' then 2 else 3 end