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

#3.计算截止当月的单月最大新增用户数、截止当月的累积用户数
select 
    *,
    max(month_add_uv) over (order by start_month) as   max_month_add_uv,
    sum(month_add_uv) over (order by start_month) as cum_sum_uv
from(
    #2.计算每月活跃用户数和每月新增用户数
    select 
        start_month,
        count(distinct uid) as mau,
        sum(new_user) as month_add_uv
    from(
        #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 new_user
        from
            exam_record)as a
    group by start_month)as b
    order by start_month

踩坑点：第一步就要判断是否为新用户