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

with tmp as (
select 
    uid,
    exam_id,
    start_time,
    date_format(start_time,'%Y%m') as ym,
    row_number() over(partition by uid order by start_time) as rank1
from exam_record)

select 
    ym,
    count(distinct uid) as mau,
    sum(if(rank1=1, 1, 0)) as month_add_uv,
    max(sum(if(rank1=1, 1, 0)) ) over (order by ym) as max_month_add_uv,
    sum(sum(if(rank1=1, 1, 0))) over(order by ym) as cum_sum_uv
from tmp
group by ym
order by ym