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

select a.video_id, round((100*fin+5*good+3*comm+2*ret)/(fresh+1),0) hot_index
from (select a.video_id,
             count(if(TIMESTAMPDIFF(SECOND,start_time,end_time)>=duration,a.video_id,null))/COUNT(a.video_id) fin,
             sum(if_like) good,
             sum(if(comment_id is not null,1,0)) comm,sum(if_retweet) ret, release_time
             from tb_user_video_log a
             join tb_video_info b
             on a.video_id = b.video_id
             group by a.video_id 
             having DATEDIFF((SELECT max(end_time) FROM tb_user_video_log),release_time)<=29) a
join (select video_id, 
             DATEDIFF((SELECT max(end_time) FROM tb_user_video_log),MAX(end_time)) fresh 
             from tb_user_video_log
             group by video_id) b
             on a.video_id = b.video_id
order by hot_index desc 
limit 3