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

with tt1 as (
    select t1.video_id,
           t1.start_time,
           t1.end_time,
           t1.if_like,
           t1.if_retweet,
           t1.comment_id,
           t2.duration,
           case when unix_timestamp(end_time) - unix_timestamp(start_time) >= t2.duration then 1 else 0 end as wanbo -- 1为完播 0为未完播
    from tb_user_video_log t1
    join tb_video_info t2
    on t1.video_id = t2.video_id
    where  release_time between date_sub((select max(end_time) from tb_user_video_log),INTERVAL 29 DAY)  and  (select max(end_time) from tb_user_video_log)
    and    end_time between date_sub((select max(end_time) from tb_user_video_log),INTERVAL 29 DAY)  and  (select max(end_time) from tb_user_video_log)

), -- select * from tt1;
tt2 as(
    select video_id,
              sum(if(wanbo = 1,1,0))/count(wanbo) as wanbolv,
              sum(if_like) as dianzanshu,
              count(comment_id) as pinglunshu,
              sum(if_retweet) as zhuanfashu,
              datediff(date_format((select max(end_time) from tb_user_video_log),'%Y-%m-%d'),date_format(max(end_time),'%Y-%m-%d')) as wubofangtianshu
    from tt1 group by video_id
) select
      video_id,
      round((100*wanbolv+5*dianzanshu+3*pinglunshu+2*zhuanfashu)*(1/(wubofangtianshu+1))) as hot_index
  from tt2 order by hot_index desc limit 3;