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

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