这个题目主要是要理解最近一个月的这个条件，那就是max(star_time)-star_time还有30的，
可以用datediff，也可以用timestampdiff，我这里用的是timestampdiff
timestampdiff(day,a.start_time,(select  max(start_time)  from tb_user_video_log))<=29
然后再把两个表用video_id连接起来，再用转发量除以播放量即可，注意按播放率降序

  • select b.tag,sum(a.if_retweet), round(sum(a.if_retweet)/count(*),3)
  • from   tb_user_video_log as a
  • left join tb_video_info as 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 3 desc