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

select
    video_id,
    round((t1*100 + t2*5 + t3*3 + t4*2)/(t5+1), 0)
from
    (
        select
            video_id,
            avg(
                timestampdiff (second, start_time, end_time) >= duration
            ) t1,
            sum(if_like) t2,
            count(comment_id) t3,
            sum(if_retweet) t4,
            datediff (date ((select max(end_time) from tb_user_video_log)),max(date(end_time))) t5
        from
            tb_user_video_log a
            join tb_video_info b using (video_id)
        where
            datediff (date ((select max(end_time) from tb_user_video_log)), date(release_time)) <= 29
        group by
            1
    ) b
order by 2 desc
limit 3