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

SELECT video_id,
       round((100*finish_rate+5*like_index+3*comment_index+2*retweet_index)/(fresh_index+1),0) hot_index
FROM(
SELECT a.video_id,
       sum(if(timestampdiff(second,a.start_time,a.end_time)-b.duration>=0,1,0))/count(a.video_id) finish_rate,
       sum(a.if_like) like_index,
       sum(if(a.comment_id is not null,1,0)) comment_index,
       sum(a.if_retweet) retweet_index,
       if(count(a.video_id)=0,datediff(date((select max(end_time) from tb_user_video_log)),date(b.release_time)),
          datediff(date((select max(end_time) from tb_user_video_log)),max(date(a.end_time)))) fresh_index
FROM tb_user_video_log a
    left join tb_video_info b
    on a.video_id=b.video_id
where DATEDIFF(date((select max(end_time) from tb_user_video_log)),DATE(b.release_time))<=29
group by a.video_id) fir_sheet
order by hot_index DESC
limit 0,3