SQL3 每类视频近一个月的转发量/率

解题思路：

统计转发量：sum(if_retweet)

统计播放量：结束观看时间 > 开始观看时间 表明播放

sum(if(timestampdiff(second, start_time, end_time) > 0, 1, 0))

最近一个月：所谓最近一个月就是找到当前最大的开始时间的对应的那一天，往前推不超过29天的都满足

select tag, sum(if_retweet) as retweet_cut, round(sum(if_retweet) / sum(if(timestampdiff(second, start_time, end_time) > 0, 1, 0)), 3) retweet_rate
from tb_user_video_log t1
left join tb_video_info t2
on t1.video_id = t2.video_id
where DATEDIFF(DATE((select max(start_time) FROM tb_user_video_log)), DATE(t1.start_time)) <= 29
group by tag
order by retweet_rate desc;