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

# 自从有用户作答记录以来，每月的试卷作答记录中月活用户数、新增用户数、截止当月的单月最大新增用户数、截止当月的累积用户数
# 月活用户数
with A as (
select date_format(start_time,'%Y%m')start_month
,count(distinct uid)mau
from exam_record
group by 1
)
# 新增用户数 找他的最小月份 再以月份分组 count一下
,B as (
select start_month,count(uid)month_add_uv from
(select uid,min(date_format(start_time,'%Y%m'))start_month
from exam_record
group by 1)b
group by 1
)
# 截止当月的单月最大新增用户数 开个窗完事 连表的时候再写 要不然会少数据
# 截止当月的累积用户数 新增+新增 同上 开个窗完事
,C as 
(select start_month,max(c)over(order by start_month)cum_sum_uv
from 
(select date_format(start_time,'%Y%m')start_month,count(distinct uid)c
from exam_record
group by 1 
order by 1)c)
# 连表去空
select start_month,mau
,ifnull(month_add_uv,0)month_add_uv
,max_month_add_uv,cum_sum_uv
from
(select start_month,mau,month_add_uv
,max(month_add_uv)over(order by start_month)max_month_add_uv
,sum(month_add_uv)over(order by start_month)cum_sum_uv
from A
left join B using(start_month)
left join C using(start_month))z
order by 1