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

# 先找到近一个月的记录,并且使用case when看每一个记录是否完全播放
with t1 as (
    select a.uid, a.video_id, a.if_follow, a.if_like, b.release_time, a.if_retweet, a.comment_id, a.end_time, 
    (case when timestampdiff(second, a.start_time, a.end_time) < b.duration then 0 else 1 end) as if_over
    from tb_user_video_log as a
    left join tb_video_info as b
    on a.video_id = b.video_id
    where release_time between (select date_sub(max(date_format(end_time, '%Y-%m-%d')), interval 29 day) from tb_user_video_log) 
    and (select max(date_format(end_time, '%Y-%m-%d')) from tb_user_video_log)
),
t2 as (
# 计算每一个视频的完播率，新鲜度， 点赞数，评论数，转发数, 新鲜度
    select video_id, sum(if_over) / count(*) as over_rate, sum(if_like) as like_cnt, sum(if(comment_id is null, 0, 1)) as comment_cnt, sum(if_retweet) as retweet_cnt, 
    1 / (timestampdiff(day, max(date_format(end_time, '%Y-%m-%d')), (select max(date_format(end_time, '%Y-%m-%d')) from tb_user_video_log)) + 1) as new_rate
    from t1
    group by video_id
)
# 计算每个视频的热度
select video_id, round((100 * over_rate + 5 * like_cnt + 3 * comment_cnt + 2 * retweet_cnt) * new_rate, 0) as hot_index
from t2
order by hot_index desc
limit 3;