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

with s1 as(select uid
,date_format(start_time,'%Y%m') start_month
,row_number()over(partition by uid order by date_format(start_time,'%Y%m')) ranking 
from exam_record) 
select start_month,mau,month_add_uv
,max(month_add_uv)over(rows between unbounded preceding and current row) max_month_add_uv 
,sum(month_add_uv)over(rows between unbounded preceding and current row) cum_sum_uv
from (
select t1.start_month,t1.mau,coalesce(t2.mau,0) month_add_uv from
(
select start_month,count(distinct uid) mau 
from s1
group by start_month
) t1
left join 
(
select start_month,count(distinct uid) mau from s1
where ranking = 1
group by start_month
) t2
on t1.start_month = t2.start_month)  t3;