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

国庆期间每类视频点赞量和转发量

http://www.nowcoder.com/practice/f90ce4ee521f400db741486209914a11

select
  tv.author author,
  date_format(tu.start_time, '%Y-%m') month,
  round(
    sum(
      case
        when if_follow = 1 then 1
        when if_follow = 2 then -1
        else 0
      end
    ) / count(tu.start_time),
    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
  left join tb_video_info tv on tu.video_id = tv.video_id
where
  year(tu.start_time) = 2021
group by
  tv.author,
  month
order by
  tv.author,
  total_fans
全部评论

相关推荐

喜欢吃蛋糕仰泳鲈鱼是我的神:字节可以找个hr 给你挂了,再放池子捞
点赞 评论 收藏
分享
评论
点赞
收藏
分享
牛客网
牛客企业服务