题解 | #近一个月发布的视频中热度最高的top3视频#
近一个月发布的视频中热度最高的top3视频
http://www.nowcoder.com/practice/0226c7b2541c41e59c3b8aec588b09ff
select g.video_id, round((100sf+5sl+3sc+2sr)/
(datediff(g.ll,g.ff)+1),0) as hot_index from
(select y.video_id,
sum(case when timestampdiff(second,y.start_time,y.end_time)
>=duration
then 1 else 0 end)/count(y.video_id) as sf,
sum(if_like) as sl,
count(comment_id) as sc,
sum(if_retweet) as sr,
date((select max(end_time) from tb_user_video_log)) as ll,
date(max(y.end_time)) as ff
from tb_video_info as x
join tb_user_video_log as y
on x.video_id=y.video_id
where datediff(date((select max(end_time)
from tb_user_video_log)),date(release_time)
)<30
group by y.video_id) as g
order by hot_index desc
limit 3
SHEIN希音公司福利 157人发布
美团二面2099人在聊
查看5道真题和解析
