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

SELECT video_id,
-- 完播率
-- 新鲜度
round((
100*sum(if(TIMESTAMPDIFF(second,start_time,end_time)>=duration,1,0))/count(video_id)+
5*sum(if_like)+3*count(comment_id)+2*sum(if_retweet))/(DATEDIFF((SELECT MAX(DISTINCT end_time) from tb_user_video_log),max(date(end_time)))+1),0) hot_index
from (
    SELECT b.tag,b.duration,a.* from tb_user_video_log a 
    JOIN (
        SELECT * from tb_video_info
        WHERE DATEDIFF((SELECT MAX(DISTINCT end_time) from tb_user_video_log),release_time ) <=29
    ) b 
    using(video_id)
) as m
GROUP BY video_id
order by hot_index desc LIMIT 0,3