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

with mau_tb as 
(select
    date_format(start_time,'%Y%m') as start_month,
    count(distinct uid) as mau
from 
    exam_record
group by 
    1),

month_add_uv_tb as 
(select
    t.first_login_date as start_month,
    count(t.uid) as month_add_uv
from
    (select
        uid,
        date_format(min(start_time),'%Y%m') as first_login_date
    from
        exam_record
    group by 
        1) t
group by 
    1),

max_month_add_uv_tb as 
(
select
    a.start_month,
    max(coalesce(b.month_add_uv,0)) over(order by a.start_month) as max_month_add_uv
from
    mau_tb a
left join
    month_add_uv_tb b on a.start_month = b.start_month
),

cum_sum_uv_tb as 
(
select
    a.start_month,
    sum(coalesce(month_add_uv,0)) over(order by start_month) as cum_sum_uv
from
    mau_tb a
left join
    month_add_uv_tb b on a.start_month = b.start_month
)

select
    a.start_month,
    a.mau,
    coalesce(b.month_add_uv,0) as month_add_uv,
    coalesce(c.max_month_add_uv,0) as max_month_add_uv,
    coalesce(d.cum_sum_uv,0) as cum_sum_uv
from
    mau_tb a
left join
    month_add_uv_tb b on a.start_month = b.start_month
left join
    max_month_add_uv_tb c on a.start_month = c.start_month
left join
    cum_sum_uv_tb d on a.start_month = d.start_month
order by 
    a.start_month