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

with zb4 as(with zb2 as (select uid,date_format(start_time,'%Y%m') start_month,start_time,first_value(start_time)
over(partition by uid order by start_time) zc_time
from exam_record)
select start_month,count(distinct uid) mau,sum(if(start_time=zc_time,1,0)) month_add_uv
from zb2
group by start_month
)
select *,max(month_add_uv) over(order by start_month) 	max_month_add_uv,
sum(month_add_uv) over(order by start_month) cum_sum_uv
from zb4
order by start_month