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

with a as(
    select 
        tvi.tag,
        sum(tuvl.if_retweet) as retweet_cnt,
        round(sum(tuvl.if_retweet)/count(tuvl.video_id),3) as retweet_rate
    from    
        tb_user_video_log as tuvl
        left join tb_video_info as tvi on tuvl.video_id = tvi.video_id
    where
        date(tuvl.start_time) between date_sub(date((select max(end_time) from tb_user_video_log)),INTERVAL 29 day) and date((select max(end_time) from tb_user_video_log))
    group by 
        tvi.tag
    order by 
        retweet_rate desc
)
select * from a