题解 | #每类视频近一个月的转发量/率#
每类视频近一个月的转发量/率
http://www.nowcoder.com/practice/a78cf92c11e0421abf93762d25c3bfad
#第一步求出日期最大值以及减去三十天后的日期
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
#第二步直接算即可
