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

select
c.video_id,
round(
   (100*c.play_rate+5*c.like_num+3*c.comment_num+2*c.retweet_num)*
   (1/(datediff((select max(left(end_time,10)) from tb_user_video_log),c.max_playdate)+1))
,0) hot_index
from
(
    select
    a.video_id,
    sum(if_like) as like_num,
    sum(if_retweet) as retweet_num,
    count(comment_id) as comment_num,
    avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0)) as play_rate,
    max(left(end_time,10)) as max_playdate
    from tb_user_video_log a right join tb_video_info b on a.video_id=b.video_id
    where datediff((select max(left(end_time,10)) from tb_user_video_log),release_time)<=29
    group by 1
)c
where c.like_num!=0
group by 1
order by 2 desc
limit 3