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

select video_id,
cast((100*wanbo+5*dianzan+3*pinglun+2*zhuanfa)*xinxian as signed)as hot_index
from
(select t1.video_id,
count(case when (timestampdiff(second,start_time,end_time))>=duration then 1 else null end)/count(*) as wanbo,
sum(if_like) as dianzan,
count(comment_id) as pinglun,
sum(if_retweet) as zhuanfa,
1/(timestampdiff(day,max(date_format(end_time,'%Y%m%d')),(select 
max(date_format(end_time,'%Y%m%d')) 
from tb_user_video_log))+1) as xinxian

from tb_user_video_log t1
join tb_video_info t2
on t1.video_id = t2.video_id

where datediff((select 
max(date_format(end_time,'%Y%m%d')) 
from tb_user_video_log),date_format(release_time,'%Y%m%d'))<=29

group by t1.video_id)a
order by hot_index desc
limit 3

二刷

易错：提交后出现三个数据，正确答案应该是2个，原因是应该用release_time去比较