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

-- 思路：依次计算出完播率，点赞数，评论数，转发数，新鲜度
-- 1. 完播率计算方式：timestampdiff函数
-- 2. 新鲜度，需要所有视频最新的end_time作为当前时间ct，和每个视频最新的end_time作差，得到未播放天数
-- 3. 过滤条件：同样需要所有视频最新的end_time作为当前时间ct，realse_time为ct近1个月的时间

with v1 as (
select
    t1.video_id,
    sum(case when timestampdiff(second,start_time,end_time) >= duration then 1 else 0 end) / count(start_time) as complete_rate,
    sum(if_like) as month_likes,
    sum(case when comment_id is null then 0 else 1 end) as month_comments,
    sum(if_retweet) as month_retweets,
    1 / (1 + timestampdiff(day, max(date(end_time)), max(ct))) as fresh_index
from tb_user_video_log t1
left join tb_video_info t2 on t1.video_id = t2.video_id
left join (select max(date(end_time)) as ct from tb_user_video_log ) t3 on 1=1
where date(release_time) between date_add(date_sub(ct, interval 1 month), interval 1 day) and ct
group by t1.video_id
) 
select
    video_id,
    round(fresh_index * (100 * complete_rate + 5 * month_likes + 3 * month_comments + 2 * month_retweets), 0) as hot_index
from v1
order by hot_index desc
limit 3;