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

# 统计出每月新增的月活跃用户才是关键的核心所在。

# 世上无难事，只怕有心人  不关心用户ID，不关心试卷ID，只关心 每月的作答纪录情况 

/*
    # 统计每月的 月活用户数，新增用户数，截止当月的单月最大新增用户数。
    select C.start_month ,
           C.mau ,
           (case when C.lastMau is null then 
                 C.mau 
                  when C.lastMau is not null and C.mau>=C.lastMau then 
                 (C.mau-C.lastMau) 
                 else 0 end 
           ) month_add_uv,
           max(C.mau-if(C.lastMau is null,0,C.lastMau)) over(order by start_month) max_month_add_uv ,
           sum((case when C.lastMau is null then 
                 C.mau 
                  when C.lastMau is not null and C.mau>=C.lastMau then 
                 (C.mau-C.lastMau) 
                 else 0 end 
           )) over(order by start_month) cum_sum_uv 
      from (
        # 统计 每月份的月活用户数，上月月活用户数
    select B.start_month,
           count(B.uid) mau ,
           lag(count(B.uid),1) over(order by B.start_month) lastMau   # 上月的用户数。
           from (
    # 统计基表的基础数据  
    select A.start_month,A.uid from (
    select date_format(ER.start_time,'%Y%m') start_month ,
           ER.exam_id,
           ER.uid 
      from  exam_record ER 
     ) A 
     group by A.start_month,A.uid  
     ) B  
     group by B.start_month 
      ) C 
      order by C.start_month  ; 
     */


     #  统计每个月的新用户   

      
         select ER.start_month,  # 每月的月份数
                count(distinct ER.uid) , # 月活用户数 
                sum(ER.new_day) month_add_uv, # 新增用户数
                max(sum(ER.new_day)) over(order by ER.start_month) max_month_add_uv, # 截止当月的最大单月新增用户数
                sum(sum(ER.new_day)) over(order by ER.start_month) cum_sum_uv # 截止当月的累计单月新增用户数
           from (
         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 
               ) ER group by ER.start_month order by ER.start_month ;