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

-- 新建表t,目的是把每个用户首月登陆时间求出来并到原表里,顺便把日期写成月
with t as(
select e1.uid,exam_id,date_format(start_time,'%Y%m') start_month,st_dt
from exam_record e1
left join (
    select uid,min(date_format(start_time,'%Y%m')) st_dt
    from exam_record
    group by uid
    )e2 on date_format(start_time,'%Y%m')=st_dt
        and e1.uid=e2.uid
)
-- 对月份聚合,mau是不重复uid计数,
-- month_add_uv是有首月值不为空的uid计数
-- max_month_add_uv是利用第一行到当前行的滑动窗口求的新用户month_add_uv最大值
-- cum_sum_uv是累加新用户month_add_uv
select start_month
    ,count(distinct uid) mau
    ,count(distinct if(st_dt is null,null,uid)) month_add_uv
    ,max(count(distinct if(st_dt is null,null,uid))) over(order by start_month rows between unbounded preceding and current row) max_month_add_uv
    ,sum(count(distinct if(st_dt is null,null,uid))) over(order by start_month) cum_sum_uv
from t
group by start_month