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

select
    tag,
    sum(if_retweet) retweet_cnt,
    round(sum(if_retweet) / count(*), 3) retweet_rate
from
    tb_user_video_log u
    join tb_video_info v on u.video_id = v.video_id
where
    end_time between date_add(
        (
            select
                max(end_time)
            from
                tb_user_video_log
        ),
        interval -30 day
    ) and (
        select
            max(end_time)
        from
            tb_user_video_log
    )
group by
    tag
order by
    retweet_rate desc