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

with t1 as (
    select a.video_id, date_format(a.start_time,'%Y-%m-%d') start_time, 
        date_format(a.end_time,'%Y-%m-%d') end_time, 
        date_format(b.release_time,'%Y-%m-%d') release_time, 
        a.if_follow, a.if_like, a.if_retweet, b.duration,
        case when a.comment_id is null then 0 else 1 end comment_id,
        unix_timestamp(end_time) - unix_timestamp(start_time) watch_times,
        max(date_format(a.end_time,'%Y-%m-%d')) over() today,
        max(date_format(a.end_time,'%Y-%m-%d')) over(partition by a.video_id) video_lastday
    from tb_user_video_log a join tb_video_info b on a.video_id = b.video_id
), t2 as (
    select distinct video_id, count(1) over(partition by video_id) watch_num,  -- 播放次数
        sum(case when watch_times>=duration then 1 else 0 end) over(partition by video_id)  finish, -- 完播次数,
        sum(if_like) over(partition by video_id) likes, -- 点赞
        sum(if_retweet) over(partition by video_id) retweets,  -- 转发
        sum(comment_id) over(partition by video_id) comments, -- 评论
        datediff(today, video_lastday) recently_watch -- 最近无播放天数
    from t1
    where datediff(today,release_time) <= 29 
), t3 as (
    select video_id, round((100*finish/watch_num + 5*likes + 3*comments + 2*retweets)* 1/(recently_watch+1),0)  hot_index
    from t2
), t4 as (
    select video_id, hot_index,row_number() over(order by hot_index desc) rn
    from t3 
), t5 as (
    select video_id,hot_index
    from t4 
    where rn <= 3
)
select * from t5