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

with a1 as (
    select Max(date(end_time)) as end_time
    from tb_user_video_log
),
a2 as (
    select tb_user_video_log.video_id, sum(case when timestampdiff(second, start_time, end_time)>= duration then 1 else 0 end)/count(*) as completerate, sum(if_like) as likecount, count(comment_id) as commentcount, sum(if_retweet) as if_retweetcount, timestampdiff(day, max(date(end_time)), (select end_time from a1)) as fresh
    from tb_user_video_log left join tb_video_info on tb_user_video_log.video_id = tb_video_info.video_id
    where date(release_time) >=date_sub((select end_time from a1), interval 29 day) and date(release_time) <= (select end_time from a1) 
    group by tb_user_video_log.video_id
),
a3 as (
    select video_id, round((100*completerate+5*likecount + 3*commentcount + 2*if_retweetcount)/(fresh+1), 0) as hot_index
    from a2
)



select video_id, hot_index
from a3
order by hot_index desc
limit 3