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

with t1 as(
select (select date(max(end_time)) from tb_user_video_log) as current_t,
uid,
tl.video_id,
start_time,
end_time,
if_follow,
if_like,
if_retweet,
comment_id,
duration,
release_time
from tb_user_video_log tl
left join tb_video_info ti
on tl.video_id=ti.video_id
)
select video_id,
round((wanbo*100+like_cnt*5+comment_count*3+retweet_cnt*2)/(wubo+1)) as hot_index
from(
select video_id,
sum(if_like) like_cnt,
count(comment_id) comment_count,
sum(if_retweet) retweet_cnt,
count( case when timestampdiff(second,start_time,end_time)>=duration then video_id else null end)/count( video_id) as wanbo,
datediff(current_t,date(max(end_time))) as wubo
from t1
where datediff(current_t,DATE(release_time))<30
group by video_id
)a
order by hot_index desc
limit 3