题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#
每个创作者每月的涨粉率及截止当前的总粉丝量
https://www.nowcoder.com/practice/d337c95650f640cca29c85201aecff84
select
author,
date_format(start_time, '%Y-%m') month,
round(sum(
case
when if_follow = 1 then 1
when if_follow = 2 then -1
else 0
end
) / count(*),3) fans_growth_rate,
sum(
sum(
case
when if_follow = 1 then 1
when if_follow = 2 then -1
else 0
end
)
) over(
partition by author
order by
date_format(start_time, '%Y-%m')
) total_fans
from
tb_user_video_log tu
join tb_video_info tv on tu.video_id = tv.video_id
where
year(start_time) = 2021
and year(end_time) = 2021
group by
author,
month
order by
author,
total_fans
做题真的得看清提纲，早知道直接用avg（）算概率了