题目难度不大,但是细节很多,一定要仔细阅读文章要求。
select video_id,
round((100*OVER_+5*like_+3*com+2*retweet)/(days+1),0) as hot_index
from
(
select vid.video_id,sum(if(TIMESTAMPDIFF(SECOND,start_time,end_time)
>=duration,1,0))/count(us.id) as OVER_,
sum(if(if_like=1,1,0)) as like_,
count(comment_id) as com,
sum(if(if_retweet=1,1,0)) as retweet,
timestampdiff(day,max(DATE_FORMAT(end_time,"%Y-%m-%d")),
(select max(DATE_FORMAT(end_time,"%Y-%m-%d"))
from tb_user_video_log)) as days
from tb_user_video_log us LEFT JOIN tb_video_info vid
using (video_id)
where release_time BETWEEN TIMESTAMPADD(DAY,-29,(select max(DATE_FORMAT(end_time,"%Y-%m-%d"))
from tb_user_video_log)) and
(select max(DATE_FORMAT(end_time,"%Y-%m-%d"))
from tb_user_video_log)
group by vid.video_id
) base
order by hot_index desc
limit 3