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

select 
    start_month,
    mau,
    ifnull(month_add_uv,0),
    max(month_add_uv) over(rows between unbounded preceding and current row) as max_month_add_uv,
    sum(month_add_uv) over(rows between unbounded preceding and current row) as cum_sum_uv
from
(
    select
    date_format(start_time, '%Y%m') as start_month,
    count(distinct uid) as mau
from exam_record er
group by start_month
)t1
left join 
(
    select 
    com_time,
    count(uid) as month_add_uv
    from
    (
        select
        uid,
        min(date_format(start_time, '%Y%m')) as com_time
        from exam_record er 
        group by uid
    ) t
    group by com_time
)t2
on t1.start_month = t2.com_time