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

select 
    y_month as start_month,mau,month_add_uv,
    max(month_add_uv) over(order by y_month) as max_month_add_uv, 
    sum(month_add_uv) over(order by y_month) as max_month_add_uv 
from(
    select y_month,
    count(uid) as mau,
    sum(case when ranking = 1 then 1 else 0 end) as month_add_uv
    from (
        select date_format(start_time,'%Y%m') as y_month,uid,
        row_number() over(partition by uid order by date_format(start_time,'%Y%m')) as ranking
        from exam_record
        group by date_format(start_time,'%Y%m'),uid)t
        group by y_month
        order by y_month)t1

首先按用户活跃的情况整理一张活跃表，再标识出用户在每个月出现的位置

然后根据这个可以算出月活及每月新增

然后根据这个算出 累计到每月的情况。

    -- 优化版本
    select y_month,
    count(uid) as mau,
    sum(case when ranking = 1 then 1 else 0 end) as month_add_uv,
    max(sum(case when ranking = 1 then 1 else 0 end)) over(order by y_month) as max_month_add_uv, 
    sum(sum(case when ranking = 1 then 1 else 0 end)) over(order by y_month) as cum_sum_uv 
    from (
        select date_format(start_time,'%Y%m') as y_month,uid,
        row_number() over(partition by uid order by date_format(start_time,'%Y%m')) as ranking
        from exam_record
        group by date_format(start_time,'%Y%m'),uid)t
        group by y_month
        order by y_month