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

一看就懂，带注释。

# 1.0 首先从tb_video_info挑选出近一个月的那些视频
with
    t2 as (
        select *
        from tb_video_info
        where date_format(release_time, '%Y-%m-%d') between date_sub((select date_format(max(end_time), '%Y-%m-%d') from tb_user_video_log), interval 29 day) and (select date_format(max(end_time), '%Y-%m-%d') from tb_user_video_log)
    )

# 2.0 明确式子各部分后直接搞定本题
#完播率 avg(if(unix_timestamp(end_time)-unix_timestamp(start_time) >= duration, 1, 0))
#点赞数 sum(if_like)
#评论数 sum(if(comment_id is not null, 1, 0))
#转发数 sum(if_retweet)
#新鲜度 1 / (datediff((select max(end_time) from tb_user_video_log), max(end_time)) + 1)

select 
    t1.video_id,
    round(((100*avg(if(unix_timestamp(end_time)-unix_timestamp(start_time) >= t2.duration, 1, 0))) + (5*sum(if_like)) + (3*sum(if(comment_id is not null, 1, 0))) + (2*sum(if_retweet)))  / (datediff((select max(end_time) from tb_user_video_log), max(end_time)) + 1), 0) as hot_index
from tb_user_video_log as t1
inner join t2
on t1.video_id = t2.video_id
group by t1.video_id
order by hot_index desc
limit 3