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

with data_bounds as (
    select
        date(max(end_time)) as max_date,
        date_sub(date(max(end_time)),interval 29 day) as min_date
    from
        tb_user_video_log
)
select
    tt.tag,
    sum(tg.if_retweet) as retweet_cnt,
    round(sum(tg.if_retweet) / count(tg.start_time),3) as retweet_rate
from
    tb_user_video_log tg
join
    tb_video_info tt on tg.video_id = tt.video_id
cross join data_bounds d
where 
    date(tg.end_time) between d.min_date and d.max_date
group by 
    tt.tag
order by 
    retweet_rate desc