题解 | #每类视频近一个月的转发量/率#

with t1 as (
    select vl.video_id, vi.tag, vl.if_retweet,
        date_format(vl.start_time,'%Y-%m-%d') start_time,
        max( date_format(vl.end_time,'%Y-%m-%d')) over() today
    from tb_user_video_log vl join tb_video_info vi on vl.video_id = vi.video_id
), t2 as (
    select video_id , tag, if_retweet
    from t1 
    where datediff(today, start_time) < 30
), t3 as (
    select distinct tag, sum(if_retweet) over(partition by tag) retweet_cut, count(1) over(partition by tag) cnt
    from t2
), t4 as (
    select tag, retweet_cut, cast((retweet_cut/cnt) as decimal(16,3)) retweet_rate
    from t3
    order by retweet_cut/cnt desc
)
select * from t4