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

解题思路：

1.判断哪些为最新用户 IF(start_time=MIN(start_time)OVER(PARTITION BY uid),1,0)

2.每月试卷作答月活用户数 count()，新增用户数sum()

3.截至当月最大新增 max() over(),累计用户数 sum() over()

解题步骤

1.判断哪些为最新用户

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

2.每月试卷作答月活用户数，新增用户数

select start_month,
count(distinct uid)mau,
sum(new_day)month_add_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)a
group by start_month
order by start_month

3.截至当月最大新增，累计用户数

select
start_month
,mau
,month_add_uv
,max(month_add_uv) over(order by start_month)
,sum(month_add_uv) over(order by start_month)cum_sum_uv 
from(
select start_month,
count(distinct uid)mau,
sum(new_day)month_add_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)a
group by start_month
order by start_month)b