思路：先把最近的互动日找出来形成一张表，然后将此表与播放表连接时加入连接条件：互动日与播放日相差29天以内即可。
select tag, sum(if_retweet) a, round(sum(if_retweet)/count(if_retweet),3) b
from tb_user_video_log tb1 left join tb_video_info tb2 using(video_id)
right join 
(select distinct video_id, max(end_time)over() max_day from tb_user_video_log where if_retweet=1) as tb3
on tb1.video_id=tb3.video_id 
and datediff(date(max_day),date(tb1.end_time)) between 0 and 29
group by tag
order by b desc