题解 | #统计活跃间隔对用户分级结果#

• 题目拆解：2021年、每个创作者、每月-> 涨粉率&截至当月粉丝量
• 排序方式：按创作者ID、总粉丝量升序

select c.author,c.month, round((add_fan-drop_fan)/play_v,3) fans_growth_rate, 
sum(add_fan-drop_fan) over(partition by author order by month) total_fans
from(
#中间表：记录每个创作者每月涨粉量，掉粉量，播放量
select author,date_format(start_time, "%Y-%m") month, 
       count(a.id) play_v, count(distinct if(if_follow = 1, a.uid,null)) add_fan,
       count(distinct if(if_follow = 2, a.uid, null)) drop_fan
from tb_user_video_log a inner join tb_video_info b
on a.video_id = b.video_id
where year(start_time) = "2021"
group by author, date_format(start_time, "%Y-%m")) c
order by author, total_fans