题解 | #每类视频近一个月的转发量/率#
每类视频近一个月的转发量/率
http://www.nowcoder.com/practice/a78cf92c11e0421abf93762d25c3bfad
这个题目主要是要理解最近一个月的这个条件，那就是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
查看19道真题和解析