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

/*输出自从有用户作答记录以来，每月的试卷作答记录中月活用户数、新增用户数、截止当月的单月最大新增用户数、截止当月的累积用户数。结果按月份升序输出。
1.格式化日期 date_format(start_time,"%Y%m") start_month
2.判断是否是新用户：if(start_time=min(start_time)over(partition by uid),1,0) new_day
3.每月月活：count(distinct uid)
4.每月新增用户数量：sum(new_day)
5.截止当月的单月最大新增用户数:max(sum(new_day)) over(order by start_month)
6.截止当月的累计用户数:sum(sum(new_day)) over(order by start_month)
*/

with t1 as(
    select 
        *
        ,date_format(start_time,"%Y%m") start_month
        ,if(start_time=min(start_time)over(partition by uid),1,0) new_day
    from exam_record
)
select 
    start_month
    ,count(distinct uid) mau
    ,sum(new_day) month_add_uv
    ,max(sum(new_day)) over(order by start_month) max_month_add_uv
    ,sum(sum(new_day)) over(order by start_month) cum_sum_uv
from t1
group by start_month