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

select
b.video_id,
round((100*(sum(b.if_ok)/count(1))+5*sum(b.if_like)+3*count(b.comment_id)
+2*sum(b.if_retweet))/(min(b.near_day)+1)) as hot_index
from
(
select
tuvl.video_id,
if(timestampdiff(second,tuvl.start_time,tuvl.end_time)>=a.duration,1,0) if_ok,
tuvl.if_like,
tuvl.if_retweet,
tuvl.comment_id,
timestampdiff(day,date(tuvl.end_time),
(select date(max(end_time)) from tb_user_video_log)) near_day
from tb_user_video_log tuvl 
inner join
(
select 
video_id,
duration
from tb_video_info 
where timestampdiff(day,date(release_time),
(select date(max(end_time)) from tb_user_video_log))<=29
) a on tuvl.video_id=a.video_id
) b
group by b.video_id
order by hot_index desc
limit 3