题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#

--SQL server

select b.author,CONVERT(varchar(7),a.start_time,120) as month,ROUND(SUM(CASE WHEN a.if_follow=1 then 1

when a.if_follow = 2 then -1 else 0 end)*1.0/COUNT(1),3) as fans_growth_rate,

SUM(SUM(CASE WHEN a.if_follow=1 then 1

when a.if_follow = 2 then -1 else 0 end)) over (partition by b.author order by CONVERT(varchar(7),a.start_time,120)) as total_fans

FROM tb_user_video_log a

LEFT JOIN tb_video_info b

ON a.video_id = b.video_id

where year(a.start_time) = 2021

group by b.author,CONVERT(varchar(7),a.start_time,120)

order by b.author,total_fans

--mySQL

select b.author,date_format(a.start_time,'%Y-%m') as month,ROUND(SUM(CASE WHEN a.if_follow=1 then 1

when a.if_follow = 2 then -1 else 0 end)*1.0/COUNT(1),3) as fans_growth_rate,

SUM(SUM(CASE WHEN a.if_follow=1 then 1

when a.if_follow = 2 then -1 else 0 end)) over (partition by b.author order by date_format(a.start_time,'%Y-%m')) as total_fans

FROM tb_user_video_log a

LEFT JOIN tb_video_info b

ON a.video_id = b.video_id

where year(a.start_time) = 2021

group by b.author,date_format(a.start_time,'%Y-%m')

order by b.author,total_fans