题解 | #每月及截止当月的答题情况#
每月及截止当月的答题情况
http://www.nowcoder.com/practice/1ce93d5cec5c4243930fc5e8efaaca1e
# 1、关键是把每月新增的用户数求出来，先把每个用户的最早出现时间找出来，再根据这个时间分组看每月有几个新增
# 2、再用开窗函数把后面两个指标查出，接着关联每月的客户数即可
# 3、注意用左连接，因为某月新增的可能为0，判断下没关联上的就是0，表示这个月么有新增
select t1.start_month,t1.mau,coalesce(t2.month_add_uv,0) as month_add_uv, max(t2.month_add_uv)over(order by t1.start_month) as max_month_add_uv, sum(t2.month_add_uv)over(order by t1.start_month) as cum_sum_uv from ( select date_format(start_time,'%Y%m') as start_month,count(distinct uid) as mau from exam_record group by date_format(start_time,'%Y%m') )t1 left join ( select min_start_month as start_month,count(1) as month_add_uv from ( select uid,min(date_format(start_time,'%Y%m')) as min_start_month from exam_record group by uid ) t group by min_start_month ) t2 on t1.start_month = t2.start_month order by t1.start_month
