题解 | #每月及截止当月的答题情况#

#题目的重点在如何定义新增用户
select ym,
count( uid),
count( if(ym=add_date,1,null)),
max(count( if(ym=add_date,1,null))) over(order by ym),
sum(count( if(ym=add_date,1,null))) over(order by ym)
from 
(
select distinct uid,date_format(start_time,'%Y%m') ym,#因为只在乎当月是否在线因此保留ID和年月全部过滤
min(date_format(start_time,'%Y%m')) over(partition by uid ) add_date #找出每个用户的首次活跃日期定义是否是新增用户
from exam_record
)t1
group by ym