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

with t1 as (
select substr(max(end_time),1,10) as end_date
from tb_user_video_log
),
t2 as (
select p1.video_id
      ,p1.end_time
      ,p1.start_time
      ,case 
                when date_add(p1.start_time,interval p2.duration second) <=p1.end_time
                then 1 else 0 end  as is_sss
      ,if_follow
      ,if_like
      ,if_retweet
      ,comment_id
      ,release_time
from  tb_user_video_log  p1 
inner join tb_video_info p2 
on  p1.video_id = p2.video_id
)
select video_id
      ,hot_index
from  (
select p1.video_id 
        ,round( (
        100*sum(is_sss) /count(1)
        + 5*sum(if_like)
        + 3*count(comment_id)
        + 2*sum(if_retweet) )
        /(datediff(max(p2.end_date),substr(max(p1.end_time),1,10)) + 1 )
        ,0) as hot_index

from t2  p1 
cross join t1 p2 
where  substr(p1.release_time,1,10) >= date_sub(p2.end_date,interval 29 day)
group by p1.video_id
) p1 
order by hot_index desc 
limit 3