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

#第一步求出日期最大值以及减去三十天后的日期

with t as(select max(start_time) max_dat
,date_sub(max(start_time),interval 30 day) min_dat
from tb_user_video_log)

select t2.tag,sum(if_retweet)
,round(sum(if_retweet)/count(*),3) ant
from tb_user_video_log t1 join tb_video_info t2 on t1.video_id=t2.video_id,t
where t1.start_time between t.min_dat and t.max_dat
group by t2.tag
order by ant desc

#第二步直接算即可