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

-- 限制条件：近一个月 -->确定当前日期，以表中最大end_time的日期作为当前日期
-- 视频完播率：
-- 新鲜度 --> 最近无播放天数

select
video_id
,round((100*avg(if_complete) + 5*sum(if_like) + 3*sum(if_comment) + 2*sum(if_retweet))/(min(diff_day)+1), 0) as hot_index
from
(
    select
    log.video_id
    ,if(timestampdiff(second,start_time,end_time) >= duration, 1, 0) if_complete
    ,if_like
    ,if_retweet
    ,if(comment_id is null, 0, 1) if_comment
    ,datediff((select max(end_time) from tb_user_video_log), end_time) diff_day
    from tb_user_video_log log
    left join tb_video_info info 
    on log.video_id = info.video_id
    where datediff((select max(end_time) from tb_user_video_log),release_time) < 30
) t
group by video_id
order by (100*avg(if_complete) + 5*sum(if_like) + 3*sum(if_comment) + 2*sum(if_retweet))/(min(diff_day)+1) desc
limit 3