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

select tag, 
sum(if_retweet) as retweet_cut, 
round(sum(if_retweet) / count(video_id), 3) as retweet_rate
from
(
    select b.tag, a.video_id, a.if_retweet, a.start_time
    from tb_user_video_log as a
    left join tb_video_info as b
    on a.video_id = b.video_id
    -- 就是有播放量的视频的最大日期 减去 30天
    where DATEDIFF(DATE((select max(start_time) FROM tb_user_video_log)), DATE(a.start_time)) <= 29
) as t1
group by tag
order by retweet_rate desc