一、明确需求：

统计有用户互动的最近一个月，每类视频的转发量和转发率（保留3位小数）

二、需求分解:

1. 最近一个月：(start_time - max(start_time))<=29
2. 联结信息表info获得tag
3. 根据tag分组，select计算
4. 排序

三、实现代码：

select b.tag
    , count(if(if_retweet = 1,a.id,null)) retweet_cut
    , round(count(if(if_retweet = 1,a.id,null)) / count(*),3) retweet_rate
from 
    (select distinct * from tb_user_video_log) a
left join (select distinct * from tb_video_info) b
    on a.video_id = b.video_id
where timestampdiff(day,a.start_time,(select max(start_time) from tb_user_video_log))<=29
group by b.tag
order by retweet_rate desc