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

易错点分析

本题对技术要求不高，重点在审题——

• 1. 完播——(end_time-start_time)>duration
• 2. 最近无播放天数——所有数据的最新日期-某一视频的最新播放日期
• 3. 近一个月发布的视频——所有数据的最新日期-某个视频的发布日期<=29

select video_id,round((100*complete_rate+5*like_cnt+3*comment_cnt
                 +2*retweet_cnt)*fresh_index,0) as hot_index
from 
    (select vl.video_id,sum(if_like) as like_cnt,
     avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0))
     as complete_rate,sum(if_retweet) as retweet_cnt,
     count(comment_id) as comment_cnt,
     1/(datediff((select max(date(end_time)) from tb_user_video_log),
               max(date(end_time)))+1) as fresh_index
     from tb_user_video_log as vl
     join tb_video_info as vi
     on vl.video_id=vi.video_id
     where datediff((select max(date(end_time)) from tb_user_video_log),
                    date(release_time))<=29
    group by video_id) as q
order by hot_index desc
limit 3