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

with t1 as
(select
    vl.video_id,
    if(timestampdiff(second, start_time, end_time)>=duration, 1, 0) as if_wan,
    date(end_time) dt,
    if_like,
    if_retweet,
    if(comment_id, 1, 0) as if_comment
from tb_user_video_log vl
left join tb_video_info vi using(video_id)
where date(release_time) between date_sub((select max(date(end_time)) from tb_user_video_log), interval 29 day)
    and (select max(date(end_time)) from tb_user_video_log)),

t2 as
(select
    video_id,
    sum(if_wan)/count(*) as wan_rate,
    sum(if_like) as liken,
    sum(if_comment) as commentn,
    sum(if_retweet) as retweetn,
    1/(datediff((select max(date(end_time)) from tb_user_video_log), max(dt))+1) as freshn
from t1
group by video_id
)
select 
    video_id,
    round((100*wan_rate+5*liken+3*commentn+2*retweetn)*freshn, 0) as hot_index
from t2
order by hot_index desc
limit 3