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

 -- 请输出自从有用户作答记录以来，每月的试卷作答记录中月活用户数、新增用户数、截止当月的单月最大新增用户数、截止当月的累积用户数。结果按月份升序输出。
with
    temp1 as (
        select
            date_format(start_time, "%Y%m") as start_month,
            count(distinct uid) as mau
        from
            exam_record
        group by
            date_format(start_time, "%Y%m")
    ),
    month_add_temp as (
        select
            start_month,
            count(uid) as month_add_uv
        from
            (
                select
                    uid,
                    min(date_format(start_time, "%Y%m")) as start_month
                from
                    exam_record
                group by
                    uid
            ) as t1
        group by
            start_month
    )
select
    start_month,
    mau,
    ifnull(month_add_uv, 0) as month_add_uv,
    max(ifnull(month_add_uv, 0)) over (
        order by
            start_month asc rows between unbounded preceding
            and current row
    ) as max_month_add_uv,
    sum(ifnull(month_add_uv, 0)) over (
        order by
            start_month asc rows between unbounded preceding
            and current row
    ) as cum_sum_uv
from
    temp1
    left join month_add_temp using (start_month)