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

select
    video_id,
    round((
        100 * finish_rate + 5 * like_cnt + 3 * comment_cnt + 2 * retweet_cnt
    ) / (no_play_cnt + 1),0) as hot_index
from
(
    select  a.video_id,  
    sum(timestampdiff(second, start_time, end_time)>=duration)/count(*) as finish_rate,
    sum(if_like) as like_cnt,
    count(comment_id) as comment_cnt,
    sum(if_retweet) as retweet_cnt,
    datediff (date ((
                        select
                            max(end_time)
                        from
                            tb_user_video_log
                    )
                ),
                max(date (end_time))
            ) as no_play_cnt
from
    (
        select
            video_id,
            duration,
            release_time
        from
            tb_video_info
    ) a
    inner join (
        select
            *
        from
            tb_user_video_log
    ) b on a.video_id = b.video_id
where
    datediff (
        date (
            (
                select
                    max(end_time)
                from
                    tb_user_video_log
            )
        ),
        date (release_time)
    ) <= 29
group by
    a.video_id
)t0
order by hot_index desc
limit 3

最近一个月发布视频概念需要理清