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

with t as(select distinct u11.video_id,            
        timestampdiff(DAY, 
                  date(max(end_time) over(partition by video_id)), 
                  t1.latest_play_date) as no_play_date_cnt
from tb_user_video_log u11, (select video_id, max(date(end_time)) over() as latest_play_date from tb_user_video_log)t1
where u11.video_id = t1.video_id),
t2 as(select s1.video_id,
        100 * avg(if(timestampdiff(second, start_time, end_time) >= duration, 1, 0)) as play_rate,
        5 * sum(if_like) as like_num,
        3 * count(comment_id) as comment_cnt,
        2 * sum(ifnull(if_retweet, 0)) as retweet_cnt
from (select *
from tb_video_info
where release_time between (select adddate(max(date(end_time)), interval -29 day) from tb_user_video_log) 
                    and (select max(date(end_time)) from tb_user_video_log))s1
left join tb_user_video_log u1 
using(video_id)
group by s1.video_id
)

select t2.video_id,
        round((play_rate + like_num + comment_cnt + retweet_cnt) / (t.no_play_date_cnt + 1), 0) as hot_index
from t2, t 
where t.video_id = t2.video_id
order by hot_index desc
limit 3