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

with
t1 as (select video_id,avg(timestampdiff (second, start_time, end_time) >= duration) as com_play_rate,sum(if_like) as sum_like,count(comment_id) as sum_comment,sum(if_retweet) as sum_retweet,datediff(date ((select max(end_time) from tb_user_video_log)),max(date(end_time))) as fresh_day from tb_user_video_log join tb_video_info using(video_id) where datediff(date((select max(end_time) from tb_user_video_log)),date(release_time))<=29 group by video_id)
select video_id,round((100*com_play_rate+5*sum_like+3*sum_comment+2*sum_retweet)/(fresh_day+1),0) as hot_index from t1 order by hot_index desc limit 3

刚开始一直不知道自己错在哪里，后来发现，求日期差不能直接相减，必须用datediff函数