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

with t1 as(select tag, if_retweet,
timestampdiff(day,start_time,(select max(start_time) FROM tb_user_video_log)) as day_from_now
from tb_user_video_log join tb_video_info using(video_id)
having day_from_now<30)

select tag,
sum(if_retweet) as reyweet_cnt,
round(sum(if_retweet)/count(*),3) as retweet_rate
from t1
group by tag
order by retweet_rate desc