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

# 最近无播放天数：这个视频最后一次播放的日期，距离最晚的天数的差值


# 下面的表给出了每个一个播放的视频的情况
with t as(
select l.video_id,
       case when timestampdiff(second,start_time,end_time) >= duration then 1 else 0 end if_full, 
       if_like,
       if_retweet,
       case when comment_id is not null then 1 else 0 end if_comment,
       datediff((select max(end_time) from tb_user_video_log),end_time) as days_not_play
from tb_user_video_log l 
join tb_video_info i on l.video_id = i.video_id
where datediff((select max(end_time) from tb_user_video_log), release_time) <= 29
)

# 根据总体的播放情况，来计算每一个视频的热度
select video_id, round((100 * sum(if_full) / count(if_full) +
                  5   * sum(if_like) +
                  3   * sum(if_comment) +
                  2   * sum(if_retweet)) *
                  1 / (min(days_not_play) + 1) ,0) hot_index
from t
group by video_id
order by hot_index desc
limit 3