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

select video_id,
round((100*a+5*b+3*c+2*d)/(e+1)) hot_index
from (
select tbu.video_id,
avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0)) a,
sum(if_like) b,
count(comment_id) c,
sum(if_retweet) d,
datediff(date((select max(end_time) from tb_user_video_log)),max(date(end_time))) e
from tb_user_video_log tbu left join tb_video_info tbi on tbu.video_id=tbi.video_id 
where datediff(date((select max(end_time) from tb_user_video_log)),date(release_time))<30 
group by tbu.video_id) as t1
order by hot_index desc
limit 3
;

参考评论区的大佬的改了datediff(date((select max(end_time) from tb_user_video_log)),max(date(end_time)))这条，但实际上我还是不理解为什么里面date((select max(end_time) from tb_user_video_log))这部分date()为什么必须写成两层括号才行，一层就报错，还请走过路过的大佬们赐教，谢谢各位