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

-- 关键点在于如何求取新增用户数
-- 1、先求新增用户数：用min(start_time)求第一次作答，如果start_time=min，则为当月新增用户，设置flag字段并标记为1
# select
#     uid,
#     date_format(start_time,'%Y-%m') as start_month,
#     if(start_time=min(start_time) over(partition by uid),1,0) 
#         as flag
# from exam_record

-- 2、用group by + 窗口函数求解
select
    replace(start_month,'-',''),
    count(distinct uid) as mau,
    sum(flag) month_add_uv,
    max(sum(flag)) over(order by start_month) 
        as max_month_add_uv,
    sum(sum(flag)) over(order by start_month) as cum_sum_uv
from (
    select
        uid,
        date_format(start_time,'%Y-%m') as start_month,
        if(start_time=min(start_time) over(partition by uid),1,0) 
            as flag
    from exam_record) t1
group by start_month