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

select t1.video_id,
round((rate_comp*100+5*sum_like+3*sum_comment+2*sum_retweet)*(1/(not_video+1)),0) as hot_index 
from (
select v1.video_id
,sum(v1.if_like) as sum_like,sum(v1.if_retweet) as sum_retweet,sum(if(v1.comment_id is null,0,1)) as sum_comment
,sum(if((TIMESTAMPDIFF(second,v1.start_time,v1.end_time)>=v2.duration),1,0))/count(*) as rate_comp
,datediff((select max(end_time) from tb_user_video_log),max(v1.end_time)) as not_video 
from tb_user_video_log as v1 
left join tb_video_info as v2 
on v1.video_id = v2.video_id 
where datediff((select max(end_time) from tb_user_video_log),v2.release_time)<=29 
group by v1.video_id) t1 
order by hot_index DESC 
limit 3