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

#首先计算每个月份的活跃用户数和新增用户数，然后将两个表进行连接，同时将新增用户表中null使用case转化为0；接着对新增用户数使用max窗口函数计算单月最大新增用户数，对新增用户数使用count窗口函数计算累积用户数，容易错误的点累积用户数是每个月新增用户数相加，不是活跃用户数相加。
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 b.*,case when month_add_uv is null then 0 else month_add_uv end as month_add_uv
FROM
(SELECT DATE_FORMAT(start_time,'%Y%m') as start_month,count(DISTINCT uid) as mau
FROM exam_record
GROUP BY DATE_FORMAT(start_time,'%Y%m')) b
left join
(
SELECT DATE_FORMAT(start_time,'%Y%m') as start_month,count(uid) as month_add_uv
FROM
(SELECT *,row_number() over (partition by uid order by start_time) as xin
FROM exam_record)a
where xin=1
GROUP BY DATE_FORMAT(start_time,'%Y%m')
) c
on b.start_month=c.start_month) d