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

# 1. 热度表
    # 筛选出最近一个月发布的
# 2. 新鲜度表
    # 最近无播放日期：现在的日期-该视频最后播放的日期。
# 3. 左联结上面两个表进行计算,选择前3

select 
    video_id,
    round((complete_rate*100+like_cnt*5+comment_cnt*3+retweet_cnt*2)*(1/(no_day+1))) as hot_index
from(
    -- 1. 热度表
    -- 每类视频的视频完播率，点赞数，评论数，转发数
    select 
        video_id,
        sum(if(timestampdiff(second,start_time,end_time)>=duration,1,0))/count(start_time) as complete_rate,
        sum(if_like) as like_cnt,
        count(comment_id) as comment_cnt,
        sum(if_retweet) as retweet_cnt
    from tb_user_video_log
    left join tb_video_info using(video_id)
    where datediff((select max(end_time) from tb_user_video_log),release_time)<=29 # 筛选最近一个月发布的视频，当前日期-发布日期<=29天
    group by video_id 
) h_t
left join 
(
    -- 2. 新鲜度表
    -- 每类视频最近无播放天数
    select
        video_id,
        datediff((select max(end_time) from tb_user_video_log),last_date) as no_day -- 最近无播放的天数
    from(
        select 
            video_id,
            max(end_time) as last_date
        from tb_user_video_log
        group by video_id
    ) f_t  
) t  using(video_id)
order by hot_index desc
limit 3