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

# 热度=(100*视频完播率+5*点赞数+3*评论数+2*转发数)/(最近无播放天数+1)
# 完播率=看完次数/观看次数
# 近一个月
with A as (
select video_id
,timestampdiff(second,start_time,end_time)d
,end_time
,if_like
,comment_id
,if_retweet
,duration
from tb_user_video_log
join tb_video_info using(video_id)
where date(start_time) > (select date_sub(max(date(end_time)),interval 30 day) from tb_user_video_log)
and date(release_time) > (select date_sub(max(date(end_time)),interval 30 day) from tb_user_video_log)
)
#无播放天数 就是今天(及最大的end_time)减去各个视频的最大(最近)播放日期。
select video_id
,round((100*sum(if(d>=duration,1,0))/count(*)+5*sum(if_like)+3*count(comment_id)+2*sum(if_retweet))/(datediff((select max(date(end_time)) from tb_user_video_log),max(date(end_time)))+1))hot_index
from A
group by 1
order by hot_index desc
limit 3