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

With t1 as (
select distinct date_format(start_time,'%Y%m') start_month,uid,
min(date_format(start_time,'%Y%m'))over(partition by uid order by date_format(start_time,'%Y%m')) min_m from exam_record
) 
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) 
from (
select start_month,count(uid) mau,
count(if(start_month=min_m,1,null)) month_add_uv from t1
group by start_month
) as t2
order by start_month
;

请输出自从有用户作答记录以来，每月的试卷作答记录中月活用户数、新增用户数、截止当月的单月最大新增用户数、截止当月的累积用户数。结果按月份升序输出。

1.月活用户数：根据月份分组后以用户id计数 count(uid)

2.某月新增用户数：用户在该月首次登陆作答，先计算出每位用户的最早登陆月份，并与当月进行对比即可

count(if(start_month=min_m,1,null)) #如果当月月份与用户首次登陆月份相同，则计数~

3.截止当月的单月最大新增用户数：计算出2后，使用开窗函数计算最大值计算即可

max(month_add_uv)over(order by start_month)

4.截止当月的累积用户数：计算出2后，同3，使用开窗函数对从有用户作答记录以来的每月新增用户求总和即可

sum(month_add_uv)over(order by start_month)

完整：

With t1 as (

select distinct date_format(start_time,'%Y%m') start_month,uid,

min(date_format(start_time,'%Y%m'))over(partition by uid order by date_format(start_time,'%Y%m')) min_m from exam_record

) #（1）从原表中提取 1.格式化月份：date_format(start_time,'%Y%m') ；2.uid,用户id；3.min(date_format(start_time,'%Y%m'))over(partition by uid order by date_format(start_time,'%Y%m')) 每位用户最早作答月份（按照用户id分组，月份升序排序）

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) #（3）从表2提取：1.月份start_month；2.当月活跃用户mau;3.每月新增用户：month_add_uv;4.截止当月最大新增用户数：max(month_add_uv)over(order by start_month) max_month_add_uv ，按月排序后求最大新增用户；5.截止当月累积用户数：因本题是从有用户作答记录开始计算的，则每月累积用户数即从首月开始的新增用户数累计求和即可：sum(month_add_uv)over(order by start_month)

from (

select start_month,count(uid) mau,

count(if(start_month=min_m,1,null)) month_add_uv from t1

group by start_month

) as t2 #（2）从表1中提取 1.月份；2.每月活跃用户：count(uid)；3、每月新增用户：判断方式-当月月份与用户首次作答月份相同count(if(start_month=min_m,1,null)) ；

order by start_month #（3）按照月份升序排序

;