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

select t1.video_id, round((complete_rate * 100 + like_total * 5 + comment_total * 3 + retweet_total * 2) / (case when datediff(latest_total_date, latest_date) <= 29 then datediff(latest_total_date, latest_date) else 29 end + 1), 0) as hot_index
from (
    select sum(case when TIMESTAMPDIFF(second, a.start_time, a.end_time) >= b.duration then 1 else 0 end) / count(a.uid) as complete_rate, 
    sum(a.if_like) as like_total, 
    sum(a.if_retweet) as retweet_total,
    count(a.comment_id) as comment_total,
    max(date(a.end_time)) as latest_date,
    a.video_id, b.release_time
    from tb_user_video_log a
    left join tb_video_info b
    on a.video_id = b.video_id
    group by a.video_id
) t1
join (
    select max(date(end_time)) as latest_total_date
    from tb_user_video_log
) t2
where datediff(latest_total_date, release_time) <= 29
order by hot_index desc limit 3

牛客的题目表达能力真的逆天 被近一个月发布卡了一万年