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

with 
# 最小登录月份
t1 as (select distinct uid, 
min(date_format(start_time, '%Y%m')) over(partition by uid) as start_mon from exam_record),
# 临时表用作统计新增
t2 as (select distinct uid, date_format(start_time, '%Y%m') as start_month, start_mon 
            from exam_record left join t1 using(uid)),
# 月活用户数
t3 as (select date_format(start_time, '%Y%m') as start_month, count(distinct uid) as mau from exam_record group by start_month),
# 后三个指标
t4 as (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, sum(if(start_month=start_mon, 1, 0)) as month_add_uv from t2 group by start_month) s)

select start_month, mau, month_add_uv, max_month_add_uv, cum_sum_uv from t3 join t4 using(start_month)
order by start_month;