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

select start_month,mau,month_add_uv,
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 date_format(start_time,'%Y%m') as start_month,
    count(distinct uid) as mau,
    count(case when (uid,start_time) in (select uid,min(start_time) as start_time 
                                         from exam_record
                                        group by  uid) then uid else null end) as month_add_uv
    from exam_record
    group by start_month
) tmp
order by start_month