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

with 
t1 as(
    select
        i.tag,
        p.video_id,
        date_format(p.start_time,'%Y-%m-%d') pdate,
        p.if_retweet
    from 
        tb_user_video_log p left join tb_video_info i using(video_id)
    where 
        p.if_follow<>0 or p.if_like<>0 or p.if_retweet<>0 or p.comment_id is not null),
t2 as(
    select
        tag,
        video_id,
        pdate,
        max(pdate) over(order by pdate desc) as max_pdate,
        if_retweet
    from t1)

select
    tag,
    sum(if_retweet) as retweet_cnt,
    round(sum(if_retweet)/count(*),3) as retweet_rate
from t2
where timestampdiff(day,pdate,max_pdate)<=29
group by tag
order by retweet_rate desc