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

# 连接表，求得所需要的字段
# 计算热度相关的因素
# 找出近一个月热度最高的top 3 
with month_time as (
    select 
        uvl.*,
        vi.duration,
        vi.release_time,
        case 
            when timestampdiff(second, start_time, end_time) >= duration then 1
            when timestampdiff(second, start_time, end_time) < duration then 0
        end as broadcast_finish #这里需要判断求值 ，记住形使用方法
    from 
        tb_user_video_log as uvl
    join 
        tb_video_info as vi on uvl.video_id = vi.video_id
    where 
        date_format(end_time, '%Y-%m-%d') between date_add('2021-10-03', interval -29 day) and '2021-10-03'
),
hot_value_factor as (
    select
        video_id,
        count(*) as broadcast_num,
        sum(broadcast_finish) as broadcast_finish_num,
        sum(if_like) as like_num,
        count(comment_id) as comment_num,
        sum(if_retweet) as retweet_num,
        max(date_format(end_time, '%Y-%m-%d')) as end_time_max 
		#这里不能直接使用max(end_time)，不然计算出来的结果很精确，跟牛客要求不一样
    from month_time
    group by video_id
)
select 
    video_id,
    round((100*round(broadcast_finish_num/broadcast_num, 1) + 5*like_num + 3*comment_num + 2*retweet_num)/(1+timestampdiff(day, end_time_max, '2021-10-03')),0) as hot_index
from hot_value_factor
order by  hot_index desc
limit 3