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

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 start_month
    ,count(distinct uid) as mau
    ,count(distinct case when start_month=first_month then uid else null end) as month_add_uv
    from 
    (
        select *
        ,date_format(start_time,"%Y%m") as start_month
        ,min(date_format(start_time,"%Y%m")) over(partition by uid order by date_format(start_time,"%Y%m")) as first_month
        from exam_record
    ) t 
    group by start_month
) t 
order by start_month