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

# 每个人最早的活跃月份=当前月份，则是新增用户
# 新增用户数：sum(case when)
# 然后基于上面的东西做窗口函数求累积,单月最大新增用户数
# 按月份升序输出

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
(select start_month,
count(distinct uid)as mau,
count(distinct case when start_month=min_month then uid else null end) as month_add_uv
from
(select uid,date_format(start_time,'%Y%m') as start_month,
min(date_format(start_time,'%Y%m'))over(partition by uid) as min_month
from exam_record)a
group by start_month)b
order by start_month