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

select tag,sum(if_retweet) as "retweet_cnt", round(sum(if_retweet)/count(1),3) as "retweet_rate"
from tb_user_video_log tvl join tb_video_info tvi
on tvl.video_id = tvi.video_id
where date(start_time) >= (
                     select date_sub(date(max(start_time)), interval 29 day)
                     from tb_user_video_log
                    )
and date(start_time) <= (
                    select date(max(start_time))
                    from tb_user_video_log
                    )
group by tag
order by retweet_rate desc