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

# 2. 求和 group by video_id
select video_id, round((100*sum(complete)/count(complete) + 5*sum(if_like) +
3*sum(comment_id)+ 2*sum(if_retweet))*(1/(datediff((select max(date_format(end_time,"%Y-%m-%d")) from tb_user_video_log), max(dt))+1)),0) as hot_index
from(
# 1. join，计算每个视频每天的完播数，点赞数，评论数，转发数
select tb_user_video_log.video_id, date_format(end_time,"%Y-%m-%d") as dt, if_like,
if(comment_id,1,0) as comment_id, if_retweet, case 
when round(timestampdiff(second,start_time,end_time)/duration,2)>=1 then 1
else 0
end as complete
from tb_user_video_log
left join tb_video_info
on tb_video_info.video_id = tb_user_video_log.video_id
where datediff((select max(date_format(end_time,"%Y-%m-%d")) from tb_user_video_log), release_time)< 30
) t
group by video_id
order by hot_index desc
limit 3