题解 | #近一个月发布的视频中热度最高的top3视频#

-- 视频完播率  timestampdiff(second,start_time,end_time)>=duration/count(1)
-- 近一个月date_sub(date(max(end_time),interval 29 day) 发布日期要大于等于这个日期
-- 点赞sum(if_like) 转发sum(if_retweet) 评论count(comment_id)
-- 新鲜度 最近无播放天数 timestampdiff(day,date(max(end_time),date(max(end_time))
select
  t1.video_id,
  round(
    (
      100 * (
        sum(
          case
            when timestampdiff(second, start_time, end_time) >= duration then 1
            else 0
          end
        ) / count(1)
      ) + 5 * sum(if_like) + 3 * count(comment_id) + 2 * sum(if_retweet)
    ) * (
      1 / (
        timestampdiff(
          day,
          date(max(end_time)),
          (
            select
              date(max(end_time))
            from
              tb_user_video_log
          )
        ) + 1
      )
    )
  ) hot_index
from
  tb_user_video_log as t1
  left join tb_video_info as t2 on t1.video_id = t2.video_id
where
  date(release_time) >= date_sub(
    (
      select
        date(max(end_time))
      from
        tb_user_video_log
    ),
    interval 29 day
  )
group by
  t1.video_id
order by
  hot_index desc
limit
  3
#MySQL##大厂#